Update avec subquery et jointures

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:

---

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:

---

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

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

Salut franckysolo.

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 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.

@+