Modifier une table par l'intermédiaire d'une vue

Version imprimable

Bonjour,

J'aimerais modifier ma table Produit grâce a l'intermédiaire d'une vue;
Est-ce possible?

Voici mes deux tables pour la creation de la vue
Code:

1 2 CREATE TABLE Produit (Id_produit NUMBER PRIMARY KEY, Marque VARCHAR (10), Prix NUMBER, Stock INTEGER); CREATE TABLE DetailCommande (Id_commande NUMBER, Id_produit NUMBER, quantite NUMBER, PRIMARY KEY(Id_commande, Id_produit), FOREIGN KEY (Id_commande) REFERENCES Commande(Id_commande) , FOREIGN KEY (Id_produit) REFERENCES Produit(Id_produit));
Voici le code pour la vue
Code:

1 2 3 CREATE OR REPLACE VIEW GestionStock AS SELECT distinct(Produit.Id_produit), Stock, Quantite, Stock-quantite as NewStock FROM Produit JOIN DetailCommande ON ( Produit.Id_Produit = DetailCommande.Id_Produit);
J'aimerais donc mettre a jour la colonne Stock de la table Produit par la colonne NewStock de la vue GestionStock;

Je vous remercie d'avance pour votre aide
Bonne soirée

Citation:

Envoyé par marineJ

Bonjour,

J'aimerais modifier ma table Produit grâce a l'intermédiaire d'une vue;
Est-ce possible?

Oui c'est non seulement possible mais aussi recommandé : il est préférable de ne requêter qu'au travers des vues

Citation:

Envoyé par marineJ

Voici le code pour la vue

CREATE OR REPLACE VIEW GestionStock
AS
SELECT distinct(Produit.Id_produit), Stock, Quantite, Stock-quantite as NewStock FROM Produit JOIN DetailCommande ON ( Produit.Id_Produit = DetailCommande.Id_Produit);

Ce serait plus lisible en précisant d'où viennent les colonnes en utilisant des alias comme dans l'exemple qui suit
Code:

1 2 3 4 5 6 7 8 9 CREATE OR REPLACE VIEW GestionStock AS SELECT distinct PR.Id_produit , PR.Stock , DC.Quantite , PR.Stock-quantite as NewStock FROM Produit as PR inner JOIN DetailCommande as DC ON DC.Id_Produit = PR.Id_Produit;
Citation:

Envoyé par marineJ

J'aimerais donc mettre a jour la colonne Stock de la table Produit par la colonne NewStock de la vue GestionStock;

Pour s'assurer que la quantité commandée dans la table "détail commande" n'a pas déjà été défalquée du stock disponible, il faut un horodatage de la ligne "détail commande" et un horodatage du stock produit pour pouvoir les comparer.
Il faut donc ajouter une colonne d'horodatage dans chacune des deux tables.

De plus, un stock est rarement lié à un produit, mais le plus souvent à un produit ET un lieu de stockage (entrepot, dépot, magasin...). A vérifier
Enfin, il faut s'assurer de quelle quantité on parle : la commande est passée mais la marchandise pas encore livrée, faut il ou non défalquer le stock ? Ca dépend si l'on parle de la quantité en stock ou de la quantité disponible pour la vente

Par ailleurs quelle est la différence entre les colonnes Stock et Stock-quantité dans la table produit ?

27/12/2017, 11h21
marineJ

1 pièce(s) jointe(s)

Bonjour,
Merci pour vos conseils

Citation:

Envoyé par escartefigue

Pour s'assurer que la quantité commandée dans la table "détail commande" n'a pas déjà été défalquée du stock disponible, il faut un horodatage de la ligne "détail commande" et un horodatage du stock produit pour pouvoir les comparer.
Il faut donc ajouter une colonne d'horodatage dans chacune des deux tables.

De plus, un stock est rarement lié à un produit, mais le plus souvent à un produit ET un lieu de stockage (entrepot, dépot, magasin...). A vérifier
Enfin, il faut s'assurer de quelle quantité on parle : la commande est passée mais la marchandise pas encore livrée, faut il ou non défalquer le stock ? Ca dépend si l'on parle de la quantité en stock ou de la quantité disponible pour la vente

Par ailleurs quelle est la différence entre les colonnes Stock et Stock-quantité dans la table produit ?

Etant novice en SQL (Je suis étudiante et j'ai eu seulement quelques heures de cours) je ne suis pas sur de bien comprendre.
Mon problème étant de remplacer la colonne Stock de la table produit par la colonne NewStock de la vue GestionStock.
Je n'ai donc aucune informations ni impératif par rapport a l'état de livraison, fournisseur...ect
Pour répondre a votre question Stock de la table Produit correspond a la quantité X que j'ai du produit A par exemple
et Stock-Quantité correspond a X-Y ou Y est la quantité commandé du produit A.
Pièce jointe 337669

Salut marineJ.

Citation:

Envoyé par marineJ

J'aimerais modifier ma table Produit grâce a l'intermédiaire d'une vue;
Est-ce possible?

Je vais faire une réponse de normand, ça dépend.

D'emblée, je suis d'accord avec Escartefigue. Il vaut mieux utiliser les vues que de travailler directement sur les tables.
Oui, mais quelle est la vue que vous allez utiliser pour faire votre mise à jour ?

Vous ne pouvez pas utiliser une vue avec jointure pour effectuer une mise à jour.
Comme dans l'exemple ci-après, la suppression et l'insertion ne fonctionnent pas.
Par contre, la mise à jour fonctionne, mais je ne garantie pas le bon résultat. En voici la preuve :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `tabone`
--------------
 
--------------
CREATE TABLE `tabone`
( `id`     INTEGER UNSIGNED  NOT NULL auto_increment PRIMARY KEY,
  `lib`    VARCHAR(255)      NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `tabone` (`lib`) values
  ('rouge'),('vert'),('jaune'),('bleu')
--------------
 
--------------
select * from `tabone`
--------------
 
+----+-------+
| id | lib   |
+----+-------+
|  1 | rouge |
|  2 | vert  |
|  3 | jaune |
|  4 | bleu  |
+----+-------+
--------------
DROP TABLE IF EXISTS `tabtwo`
--------------
 
--------------
CREATE TABLE `tabtwo`
( `id`     INTEGER UNSIGNED  NOT NULL auto_increment PRIMARY KEY,
  `mess`   VARCHAR(255)      NOT NULL,
  `clef`   INTEGER UNSIGNED  NOT NULL,
  CONSTRAINT `FK_CLEF` FOREIGN KEY (`clef`) REFERENCES `tabone` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `tabtwo` (`mess`,`clef`) values
  ('un', 4),('deux', 3),('trois', 2),('quatre', 1)
--------------
 
--------------
select * from `tabtwo`
--------------
 
+----+--------+------+
| id | mess   | clef |
+----+--------+------+
|  1 | un     |    4 |
|  2 | deux   |    3 |
|  3 | trois  |    2 |
|  4 | quatre |    1 |
+----+--------+------+
--------------
drop view if exists `vue`
--------------
 
--------------
create view `vue` as
  select      t1.id    as id1,
              t1.clef,
              t1.mess,
              t2.id    as id2,
              t2.lib
        from  `tabtwo` as t1
 
  inner join  `tabone` as t2
          on  t2.id = t1.clef
--------------
 
--------------
select * from `vue`
--------------
 
+-----+------+--------+-----+-------+
| id1 | clef | mess   | id2 | lib   |
+-----+------+--------+-----+-------+
|   1 |    4 | un     |   4 | bleu  |
|   2 |    3 | deux   |   3 | jaune |
|   3 |    2 | trois  |   2 | vert  |
|   4 |    1 | quatre |   1 | rouge |
+-----+------+--------+-----+-------+
--------------
insert into `vue` (`id1`,`lib`) values (10,'bla bla bla')
--------------
 
ERROR 1393 (HY000) at line 93: Can not modify more than one base table through a join view 'base.vue'
--------------
update `vue`
   set lib = 'vingt-cinq'
 where id2 = 1
--------------
 
--------------
delete from `vue`
where id1 = 2
--------------
 
ERROR 1395 (HY000) at line 107: Can not delete from join view 'base.vue'
--------------
select * from `vue`
--------------
 
+-----+------+--------+-----+------------+
| id1 | clef | mess   | id2 | lib        |
+-----+------+--------+-----+------------+
|   1 |    4 | un     |   4 | bleu       |
|   2 |    3 | deux   |   3 | jaune      |
|   3 |    2 | trois  |   2 | vert       |
|   4 |    1 | quatre |   1 | vingt-cinq |
+-----+------+--------+-----+------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Je vous déconseille d'utiliser une vue avec jointure pour effectuer une mise à jour ou quoi que ce soit d'autre.
Comme vous désirez faire un calcul, ce n'est pas la bonne approche.

Citation:

Envoyé par marineJ

J'aimerais donc mettre à jour la colonne Stock de la table Produit par la colonne NewStock de la vue GestionStock;

Avez-vous accès à vos tables ? Si la réponse est OUI alors voici comme faire :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `produit`
--------------
 
--------------
CREATE TABLE `produit`
( `id_produit`    integer unsigned not null auto_increment primary key,
  `marque`        varchar(255)     not null,
  `prix`          decimal(15,2)    not null,
  `stock`         integer unsigned not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `produit` (`marque`,`prix`,`stock`) values
  ('Marque Un',     15.25, 30),
  ('Marque Deux',   17.75, 20),
  ('Marque Trois',  22.45,  5),
  ('Marque Quatre', 11.50,  2)
--------------
 
--------------
select * from `produit`
--------------
 
+------------+---------------+-------+-------+
| id_produit | marque        | prix  | stock |
+------------+---------------+-------+-------+
|          1 | Marque Un     | 15.25 |    30 |
|          2 | Marque Deux   | 17.75 |    20 |
|          3 | Marque Trois  | 22.45 |     5 |
|          4 | Marque Quatre | 11.50 |     2 |
+------------+---------------+-------+-------+
--------------
DROP TABLE IF EXISTS `DetailCommande`
--------------
 
--------------
CREATE TABLE `DetailCommande`
( `id_commande`   integer unsigned not null,
  `id_produit`    integer unsigned not null,
  `quantite`      integer unsigned not null,
  primary key (`Id_commande`,`Id_produit`),
 
  CONSTRAINT `FK_PRODUIT`  FOREIGN KEY (`id_produit`)  REFERENCES `produit`  (`id_produit`)  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `DetailCommande` (`id_commande`,`id_produit`,`quantite`) values
  (101, 1, 5),
  (102, 2, 2),
  (103, 3, 1),
  (104, 4, 2),
  (105, 1, 9),
  (106, 3, 4)
--------------
 
--------------
select * from `DetailCommande`
--------------
 
+-------------+------------+----------+
| id_commande | id_produit | quantite |
+-------------+------------+----------+
|         101 |          1 |        5 |
|         102 |          2 |        2 |
|         103 |          3 |        1 |
|         104 |          4 |        2 |
|         105 |          1 |        9 |
|         106 |          3 |        4 |
+-------------+------------+----------+
--------------
select  t1.id_produit,
        t2.stock,
        t1.quantite,
        t2.stock - t1.quantite as newstock
 
      from  DetailCommande as t1
 
inner join  produit        as t2
        on  t2.id_produit = t1.id_produit
 
  order by  id_commande
--------------
 
+------------+-------+----------+----------+
| id_produit | stock | quantite | newstock |
+------------+-------+----------+----------+
|          1 |    30 |        5 |       25 |
|          2 |    20 |        2 |       18 |
|          3 |     5 |        1 |        4 |
|          4 |     2 |        2 |        0 |
|          1 |    30 |        9 |       21 |
|          3 |     5 |        4 |        1 |
+------------+-------+----------+----------+
--------------
commit
--------------
 
--------------
update  `produit` as t1
   set  t1.stock = t1.stock - (select  sum(t2.quantite)
                                 from  `DetailCommande` as t2
                                where  t2.id_produit = t1.id_produit
                              )
--------------
 
--------------
select * from `produit`
--------------
 
+------------+---------------+-------+-------+
| id_produit | marque        | prix  | stock |
+------------+---------------+-------+-------+
|          1 | Marque Un     | 15.25 |    16 |
|          2 | Marque Deux   | 17.75 |    18 |
|          3 | Marque Trois  | 22.45 |     0 |
|          4 | Marque Quatre | 11.50 |     0 |
+------------+---------------+-------+-------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Si la réponse est NON, je n'ai pas de solution à vous fournir.
Avez-vous une vie sur la table "produit" et une autre vue sur la table "DetailCommande" ?
Si c'est le cas alors remplacé les noms des tables par les noms des vues.

Dans vos vues, avez-vous des restrictions d'ordre sécuritaire ?

@+

27/12/2017, 13h22
marineJ

Bonjour,

J'ai accès à mes tables et n'est aucune restrictions.
Je vais essayer de faire ce que vous avez fait et comprendre chaque ligne de code.
Je reviendrais vers vous en cas d'incompréhension, si vous me le permettez.
Je vous remercie grandement .
27/12/2017, 13h55
marineJ

J'ai bien compris vos lignes de code,
J'ai cependant une question
Comment mettre une contrainte sur le nouveau stock dans le sens ou le stock initial a zéro reste a zéro en cas de nouvelle commande et ne passe pas dans les négatifs?
Merci

Salut marineJ.

Citation:

Envoyé par marineJ

Comment mettre une contrainte sur le nouveau stock dans le sens ou le stock initial à zéro reste à zéro en cas de nouvelle commande et ne passe pas dans les négatifs ?

Puisque c'est votre demande, je vous communique, selon mon exemple, votre contrainte sur le nouveau stock :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 -------------- update `produit` as t1 inner join (select id_produit, sum(quantite) as cumul from `DetailCommande` group by id_produit ) as t2 on t2.id_produit = t1.id_produit set t1.stock = t1.stock - t2.cumul where t1.stock >= t2.cumul -------------- -------------- select * from `produit` -------------- +------------+---------------+-------+-------+ | id_produit | marque | prix | stock | +------------+---------------+-------+-------+ | 1 | Marque Un | 15.25 | 16 | | 2 | Marque Deux | 17.75 | 18 | | 3 | Marque Trois | 22.45 | 5 | | 4 | Marque Quatre | 11.50 | 0 | +------------+---------------+-------+-------+
J'ai modifié la commande 103 de la table `DetailCommande` en mettant 2 pour la quantité au lieu de 1 comme avant.
Le résultat est que le nouveau stock n'a pas été modifié pour le produit 3. Pourquoi ?
Le stock est de 5 tandis que le nouveau stock (cumul) donne 6.

Votre contrainte provoque un problème d'intégrité. Pourquoi ?
Certaines lignes ont été mises à jour, tandis que d'autre ne l'ont pas été.
Comment allez-vous faire pour distinguer celles qui n'ont pas été impactées ?

Comme l'a indiqué Escartefigue, il manque dans vos table, une colonne d'horodatage.
L'une dans la table `DetailCommande` et l'autre dans la table `Produit`.
Pour impacter vos stocks, l'horodatage de `DetailCommande` doit être inférieur strictement à l'horodatage de `Produit`.
Ainsi, vous n'allez pas impacter à nouveau le stock d'un produit, quand vous lancerez une seconde fois la mise à jour des stocks le même jour.

Vu que vous utilisez une vue, le mieux est de la modifier afin d'introduire l'ancien stocke, et le nouveau stock, comme ci-après, sur le même exemple :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 -------------- create view `vue` as select t1.id_produit, t1.stock as oldstock, t2.cumul, t1.stock - t2.cumul as newstock from produit as t1 inner join (select id_produit, sum(quantite) as cumul from `DetailCommande` group by id_produit ) as t2 on t2.id_produit = t1.id_produit order by id_produit -------------- -------------- select * from `vue` -------------- +------------+----------+-------+----------+ | id_produit | oldstock | cumul | newstock | +------------+----------+-------+----------+ | 1 | 30 | 14 | 16 | | 2 | 20 | 2 | 18 | | 3 | 5 | 6 | -1 | | 4 | 2 | 2 | 0 | +------------+----------+-------+----------+
Bien sûr, la vue devra être exécutée afin de vérifier que le nouveau stock ne devienne pas négatif.
Dans l'exemple ci-dessus, je n'ai fait aucune restriction sur les lignes.
Normalement, vous devez vous restreindre au produit qui va subir cette mise à jour :
Code:

1 2 3 4 5 6 7 8 9 10 11 -------------- select * from `vue` where id_produit = 3 -------------- +------------+----------+-------+----------+ | id_produit | oldstock | cumul | newstock | +------------+----------+-------+----------+ | 3 | 5 | 6 | -1 | +------------+----------+-------+----------+
Tandis que pour la mise à jour, vous faites :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 -------------- update `produit` as t1 inner join (select id_produit, sum(quantite) as cumul from `DetailCommande` group by id_produit ) as t2 on t2.id_produit = t1.id_produit set t1.stock = t1.stock - t2.cumul where t1.stock >= t2.cumul and t1.id_produit = 3
@+

28/12/2017, 12h50
marineJ

Bonjour,
Merci pour votre aide.
Cependant je n’ai pas vu la notion d’horodatage en cours qu’est ce que c’est ?
Et dans votre code au final le stock du produit 3 reste à -1 et non a 0.
Je ne saisis pas bien ce qui a changer.

Salut MarineJ.

Citation:

Envoyé par MarineJ

Cependant je n’ai pas vu la notion d’horodatage en cours qu’est ce que c’est ?

Pour l'horodatage, c'est une colonne de type "timestamp", comprenant la date, l"heure et les millionièmes de seconde.

Citation:

Envoyé par MarineJ

Et dans votre code au final le stock du produit 3 reste à -1 et non a 0.

Non, vous n'avez pas bien compris mon message. Pourquoi ?

1) dans mon premier exemple, le test démontre que le nouveau stock est supérieur à l'ancien stock et de ce fait, je ne modifie pas la ligne.
Autrement dit, la requête "update", pour le produit = 3, ne modifie pas le stock.
La ligne qui n'a pas été modifié, je l'ai mis en rouge.

2) dans mon deuxième exemple, il s'agit de la vue, mais le stock dans la table n'a pas été modifié.
Il suffit après l'exécution de la vue, de faire un select sur la table, pour s'apercevoir que le stock n'a pas été modifié et reste à son ancienne valeur.

Comment procéder ?
Dans un premier temps, vous interrogez la vue (mon troisième exemple) en vous positionnant sur l'identifiant du produit = 3.
Si le stock devient négatif alors vous ne faites pas la suite, sinon vous pouvez lancer la mise à jour du stock.
Mon quatrième exemple vient modifier la ligne pour l'identifiant produit = 3.
Dans ces deux exemples, j'ai ajouté la contrainte "where id_produit = 3" afin de ne traiter que ce cas là.

Sinon, je n'ai pas bien compris ce que vous désirez faire.
Est-ce que vous désirez mettre votre stock à zéro, quand celui-ci devient négatif ?
Si c'est cela alors voici comment procéder :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 -------------- update `produit` as t1 inner join (select id_produit, sum(quantite) as cumul from `DetailCommande` group by id_produit ) as t2 on t2.id_produit = t1.id_produit set t1.stock = case when t1.stock - t2.cumul >=0 then t1.stock - t2.cumul else 0 end -------------- -------------- select * from `produit` -------------- +------------+---------------+-------+-------+ | id_produit | marque | prix | stock | +------------+---------------+-------+-------+ | 1 | Marque Un | 15.25 | 16 | | 2 | Marque Deux | 17.75 | 18 | | 3 | Marque Trois | 22.45 | 0 | | 4 | Marque Quatre | 11.50 | 0 | +------------+---------------+-------+-------+
@+