Discussion :

[franckysolo]

Bonjour,

j'ai un soucis avec une requête de mise à jour d'un champ avec une sous-requête

voici le schéma succinct de mes tables (j'ai omis les champs inutiles pour la requête)

orders: id, user_id
order_prestation : order_id, qty
prestation: id, number
event_session: user_id, prestation_id, total

Un bug dans mon appli a fait que le champ total de la table event_session a été modifié avec une mauvaise valeur sur certaines entrées

il doit prendre la valeur event_session.total = (prestation.number * order_prestation.qty)

J'ai créer une requête pour vérifier combien d'entrés on subit le bug:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
SELECT
orders.id as facture_id,
orders.ref as facture_ref,
event_session.id as event_session_id,
order_prestation.id as order_prestation_id,
event_session.user_id as userid,
event_session.prestation_id as presta_id,
order_prestation.prestation_id as order_prestation_prestation_id,
event_session.total as total_a_corriger,
(prestation.number * order_prestation.qty) as total_ok
FROM order_prestation
JOIN orders ON orders.id = order_prestation.order_id
JOIN event_session ON order_prestation.prestation_id = event_session.prestation_id
JOIN prestation ON prestation.id = order_prestation.prestation_id
WHERE event_session.total != (prestation.number * order_prestation.qty)
AND order_prestation.prestation_id = event_session.prestation_id
AND event_session.user_id = orders.user_id

Je voulais les corriger manuellement pensant qu'il n'y en avait pas beaucoup, mais en fait j'ai 60 bdd à mettre à jour j'ai regarder 5 bdd dans 2 j'ai pas d'erreur, les trois autres j'ai 27, 150, et plus de 300 entrées à modifier...

J'ai donc opté pour une maj auto avec un requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
UPDATE event_session SET event_session.total = (
  SELECT
  (prestation.number * order_prestation.qty) as total_ok
  FROM order_prestation
  JOIN orders ON orders.id = order_prestation.order_id
  JOIN event_session ON order_prestation.prestation_id = event_session.prestation_id
  JOIN prestation ON prestation.id = order_prestation.prestation_id
  WHERE event_session.total != (prestation.number * order_prestation.qty)
  AND order_prestation.prestation_id = event_session.prestation_id
  AND event_session.user_id = orders.user_id
)

Mais évidemment ça ne passe pas : `You can't specify target table 'event_session' for update in FROM clause`
Je sèche!
Si quelqu'un a une idée je suis preneur.

Merci

[ddaweb]

Bonsoir,

Si j'ai bien compris la première requête détecte les erreurs apparues.
Vous pourriez donc faire la màj du champs à ce moment là avec que les données nécessaire ? ... au lieu de refaire une requête identique pour la mise à jour.

Bàv,
DDA

[Artemus24]

Salut franckysolo.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
--------------
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 `users`
--------------
 
--------------
CREATE TABLE `users`
( `id`       integer unsigned auto_increment not null  primary key,
  `nom`      varchar(255)                    not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `users` (`nom`) values
  ('Un'),('Deux'),('Trois'),('Quatre')
--------------
 
--------------
select * from `users`
--------------
 
+----+--------+
| id | nom    |
+----+--------+
|  1 | Un     |
|  2 | Deux   |
|  3 | Trois  |
|  4 | Quatre |
+----+--------+
--------------
DROP TABLE IF EXISTS `orders`
--------------
 
--------------
CREATE TABLE `orders`
( `id`       integer unsigned auto_increment not null  primary key,
  `ref`      varchar(255)                    not null,
  `user_id`  integer unsigned                not null,
  CONSTRAINT `FK_USERS` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `orders` (`ref`,`user_id`) values
  ('ref 01',1),('ref 02',2),('ref 03',3),('ref 04',4),
  ('ref 05',1),('ref 06',2),('ref 07',3),('ref 08',4)
--------------
 
--------------
select * from `orders`
--------------
 
+----+--------+---------+
| id | ref    | user_id |
+----+--------+---------+
|  1 | ref 01 |       1 |
|  2 | ref 02 |       2 |
|  3 | ref 03 |       3 |
|  4 | ref 04 |       4 |
|  5 | ref 05 |       1 |
|  6 | ref 06 |       2 |
|  7 | ref 07 |       3 |
|  8 | ref 08 |       4 |
+----+--------+---------+
--------------
DROP TABLE IF EXISTS `prestation`
--------------
 
--------------
CREATE TABLE `prestation`
( `id`             integer unsigned auto_increment not null  primary key,
  `number`   decimal(15,2)                         not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `prestation` (`number`) values
  ( 2.0),( 5.0),( 7.0),(10.0),(12.0),(15.0),(17.0),(20.0),(22.0),(25.0),(27.0),(30.0),(32.0),(35.0),(37.0),(40.0)
--------------
 
--------------
select * from `prestation`
--------------
 
+----+--------+
| id | number |
+----+--------+
|  1 |   2.00 |
|  2 |   5.00 |
|  3 |   7.00 |
|  4 |  10.00 |
|  5 |  12.00 |
|  6 |  15.00 |
|  7 |  17.00 |
|  8 |  20.00 |
|  9 |  22.00 |
| 10 |  25.00 |
| 11 |  27.00 |
| 12 |  30.00 |
| 13 |  32.00 |
| 14 |  35.00 |
| 15 |  37.00 |
| 16 |  40.00 |
+----+--------+
--------------
DROP TABLE IF EXISTS `order_prestation`
--------------
 
--------------
CREATE TABLE `order_prestation`
( `id`             integer unsigned auto_increment not null  primary key,
  `order_id`       integer unsigned                not null,
  `prestation_id`  integer unsigned                not null,
  `qty`            decimal(15,2)                   not null,
  CONSTRAINT `FK_PRESTATION_1` FOREIGN KEY (`order_id`)      REFERENCES `orders`     (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_PRESTATION_2` FOREIGN KEY (`prestation_id`) REFERENCES `prestation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `order_prestation` (`order_id`,`prestation_id`,`qty`) values
  (1, 7,10.0),(2, 4,12.0),(3,11,14.0),(4,16,16.0),(5, 9,10.0),(6, 2,12.0),(7, 6,14.0),(8, 5,16.0),
  (1,15,10.0),(2,12,12.0),(3, 3,14.0),(4, 8,16.0),(5, 1,10.0),(6,10,12.0),(7,14,14.0),(8,13,16.0)
--------------
 
--------------
select * from `order_prestation`
--------------
 
+----+----------+---------------+-------+
| id | order_id | prestation_id | qty   |
+----+----------+---------------+-------+
|  1 |        1 |             7 | 10.00 |
|  2 |        2 |             4 | 12.00 |
|  3 |        3 |            11 | 14.00 |
|  4 |        4 |            16 | 16.00 |
|  5 |        5 |             9 | 10.00 |
|  6 |        6 |             2 | 12.00 |
|  7 |        7 |             6 | 14.00 |
|  8 |        8 |             5 | 16.00 |
|  9 |        1 |            15 | 10.00 |
| 10 |        2 |            12 | 12.00 |
| 11 |        3 |             3 | 14.00 |
| 12 |        4 |             8 | 16.00 |
| 13 |        5 |             1 | 10.00 |
| 14 |        6 |            10 | 12.00 |
| 15 |        7 |            14 | 14.00 |
| 16 |        8 |            13 | 16.00 |
+----+----------+---------------+-------+
--------------
DROP TABLE IF EXISTS `event_session`
--------------
 
--------------
CREATE TABLE `event_session`
( `id`             integer unsigned auto_increment not null  primary key,
  `prestation_id`  integer unsigned                not null,
  `user_id`        integer unsigned                not null,
  `total`          decimal(15,2)                   not null,
  `calc`           decimal(15,2)                   not null,
  CONSTRAINT `FK_EVENT_SESSION_1` FOREIGN KEY (`prestation_id`) REFERENCES `prestation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_EVENT_SESSION_2` FOREIGN KEY (`user_id`)       REFERENCES `users`      (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `event_session` (`prestation_id`,`user_id`,`total`,`calc`) values
  (2,4,33,0),(4,3,17,0),(6,2,36,0),(8,1,41,0),(1,4,33,0),(3,3,17,0),(5,2,36,0),(7,1,41,0)
--------------
 
--------------
select * from `event_session`
--------------
 
+----+---------------+---------+-------+------+
| id | prestation_id | user_id | total | calc |
+----+---------------+---------+-------+------+
|  1 |             2 |       4 | 33.00 | 0.00 |
|  2 |             4 |       3 | 17.00 | 0.00 |
|  3 |             6 |       2 | 36.00 | 0.00 |
|  4 |             8 |       1 | 41.00 | 0.00 |
|  5 |             1 |       4 | 33.00 | 0.00 |
|  6 |             3 |       3 | 17.00 | 0.00 |
|  7 |             5 |       2 | 36.00 | 0.00 |
|  8 |             7 |       1 | 41.00 | 0.00 |
+----+---------------+---------+-------+------+
--------------
drop view if exists `vue`
--------------
 
--------------
create view `vue` as
  select  t1.id,
          t1.user_id,
          t1.prestation_id,
          cast(sum(t2.qty * t3.number) as decimal(15,2))  as total
 
             from  event_session       as t1
 
  left outer join  order_prestation    as t2
               on  t2.prestation_id    = t1.prestation_id
 
  left outer join  prestation          as t3
               on  t3.id               = t1.prestation_id
              and  t3.id               = t2.prestation_id
 
         group by  t1.id
         order by  t1.id
--------------
 
--------------
select * from `vue`
--------------
 
+----+---------+---------------+--------+
| id | user_id | prestation_id | total  |
+----+---------+---------------+--------+
|  1 |       4 |             2 |  60.00 |
|  2 |       3 |             4 | 120.00 |
|  3 |       2 |             6 | 210.00 |
|  4 |       1 |             8 | 320.00 |
|  5 |       4 |             1 |  20.00 |
|  6 |       3 |             3 |  98.00 |
|  7 |       2 |             5 | 192.00 |
|  8 |       1 |             7 | 170.00 |
+----+---------+---------------+--------+
--------------
select           t8.id               as facture_id,
                 t8.ref              as facture_ref,
                 t4.id               as event_session_id,
                 t6.id               as order_prestation_id,
                 t5.user_id          as userid,
                 t5.prestation_id    as presta_id,
                 t6.prestation_id    as order_prestation_prestation_id,
                 t4.total            as total_a_corriger,
                 t5.total            as total_ok
 
           from  event_session       as t4
 
left outer join  (  select  t1.id,
                            t1.user_id,
                            t1.prestation_id,
                            cast(sum(t2.qty * t3.number) as decimal(15,2))  as total
 
                      from  event_session       as t1
 
           left outer join  order_prestation    as t2
                        on  t2.prestation_id    = t1.prestation_id
 
           left outer join  prestation          as t3
                        on  t3.id               = t1.prestation_id
                       and  t3.id               = t2.prestation_id
 
                  group by  t1.id, t1.user_id, t1.prestation_id  ) as t5
             on  t5.id               = t4.id
 
left outer join  order_prestation    as t6
             on  t6.prestation_id    = t4.prestation_id
 
left outer join  prestation          as t7
             on  t7.id               = t4.prestation_id
            and  t7.id               = t6.prestation_id
 
left outer join  orders              as t8
             on  t8.user_id          = t4.user_id
 
left outer join  users               as t9
             on  t9.id               = t4.user_id
            and  t9.id               = t8.user_id
 
       order by  t4.id
--------------
 
+------------+-------------+------------------+---------------------+--------+-----------+--------------------------------+------------------+----------+
| facture_id | facture_ref | event_session_id | order_prestation_id | userid | presta_id | order_prestation_prestation_id | total_a_corriger | total_ok |
+------------+-------------+------------------+---------------------+--------+-----------+--------------------------------+------------------+----------+
|          4 | ref 04      |                1 |                   6 |      4 |         2 |                              2 |            33.00 |    60.00 |
|          8 | ref 08      |                1 |                   6 |      4 |         2 |                              2 |            33.00 |    60.00 |
|          3 | ref 03      |                2 |                   2 |      3 |         4 |                              4 |            17.00 |   120.00 |
|          7 | ref 07      |                2 |                   2 |      3 |         4 |                              4 |            17.00 |   120.00 |
|          2 | ref 02      |                3 |                   7 |      2 |         6 |                              6 |            36.00 |   210.00 |
|          6 | ref 06      |                3 |                   7 |      2 |         6 |                              6 |            36.00 |   210.00 |
|          1 | ref 01      |                4 |                  12 |      1 |         8 |                              8 |            41.00 |   320.00 |
|          5 | ref 05      |                4 |                  12 |      1 |         8 |                              8 |            41.00 |   320.00 |
|          4 | ref 04      |                5 |                  13 |      4 |         1 |                              1 |            33.00 |    20.00 |
|          8 | ref 08      |                5 |                  13 |      4 |         1 |                              1 |            33.00 |    20.00 |
|          3 | ref 03      |                6 |                  11 |      3 |         3 |                              3 |            17.00 |    98.00 |
|          7 | ref 07      |                6 |                  11 |      3 |         3 |                              3 |            17.00 |    98.00 |
|          2 | ref 02      |                7 |                   8 |      2 |         5 |                              5 |            36.00 |   192.00 |
|          6 | ref 06      |                7 |                   8 |      2 |         5 |                              5 |            36.00 |   192.00 |
|          1 | ref 01      |                8 |                   1 |      1 |         7 |                              7 |            41.00 |   170.00 |
|          5 | ref 05      |                8 |                   1 |      1 |         7 |                              7 |            41.00 |   170.00 |
+------------+-------------+------------------+---------------------+--------+-----------+--------------------------------+------------------+----------+
--------------
update      `event_session` as t1
inner join  `vue`           as t2
        on  t2.id = t1.id
 
       set  t1.calc = t2.total
--------------
 
--------------
select * from `event_session`
--------------
 
+----+---------------+---------+-------+--------+
| id | prestation_id | user_id | total | calc   |
+----+---------------+---------+-------+--------+
|  1 |             2 |       4 | 33.00 |  60.00 |
|  2 |             4 |       3 | 17.00 | 120.00 |
|  3 |             6 |       2 | 36.00 | 210.00 |
|  4 |             8 |       1 | 41.00 | 320.00 |
|  5 |             1 |       4 | 33.00 |  20.00 |
|  6 |             3 |       3 | 17.00 |  98.00 |
|  7 |             5 |       2 | 36.00 | 192.00 |
|  8 |             7 |       1 | 41.00 | 170.00 |
+----+---------------+---------+-------+--------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Il y a déjà un problème, votre requête est fausse.

La table "event_session" où vous devez vérifier l'exactitude du total, est lui-même dépendant de toutes les lignes de la table "order_prestation" ayant les même "prestation_id" et "user_id".
Donc, pour obtenir le bon total, vous devez faire la sommation du produit "qty * number".
Si vous obtenez de bon résultat, c'est que la sommation est sur 1 seule ligne.

Autre problème avec la requête, il y a des colonnes à NULL ! Ce qui sous-entend que la jointure ne sait pas faite.
J'ai repris la requête afin de faire les bonnes jointures.

En ce qui concerne le update, vous devez faire une jointure entre deux tables (c'est le plus simple).
De ce fait, la deuxième table est nécessairement une vue.

Et bien sûr la vue, va vous recalculer le bon total.

@+