Discussion :

[ghohm]

Bonjour,

Je cherche à mettre à jour plusieurs lignes en même temps mais je pense que je ne m'y prends pas correctement !?

Code :

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

1
2
3
4
5
6
7
8
9
UPDATE `mantis_custom_field_string_table` SET `value` = (
SELECT `new_value`
FROM `mantis_bug_history_table`
WHERE `field_name` = 'Lot PROJET'
AND `bug_id` in (4,20,67,70,79,81,82,89,90,98,123,139,149,151,161,162,175,184,185,190,193,194,195,196,198,200,211,219,222,229,230,231,232,233,235,237,239,250,252,263,293,294,307,310,311,315,319,320,327,335,336,349,356,360,361,362,365,393,394,399,401,402,405,409,436,442,474,496,521,534,535,536,542,543,544,548,554,555,572,574,582,584,585,603,669,671,685,688,695,702,714,735,746,767,795,805,843,848,858,868,878,883,886,887,912,922,924,925,935,937,966,969,971,976,983,985,986,989,994,1001,1002,1005,1009,1021,1022,1023,1025,1037,1058,1062,1063,1076,1077,1089,1090,1093,1094,1101,1102,1110,1136,1150,1154,1156,1161,1162,1164,1168,1170,1176,1179,1180,1184,1189,1190,1194,1195,1199,1205,1206,1208,1210,1212,1214,1215,1220,1221,1227,1229,1230,1233,1235,1239,1251,1253,1257,1258,1264,1267,1268,1270,1271,1272,1274,1275,1276,1277,1283,1285,1289,1292,1295,1300,1309,1313,1317,1322,1323,1324,1326,1329,1330,1332,1333,1337,1339,1351,1352,1353,1363,1368,1371,1373,1376,1381,1382,1386,1387,1388,1389,1391,1398,1406,1417,1420,1422,1423,1424,1430,1438,1447,1455,1465,1469,1481,1485,1488,1490,1496,1502,1508,1511,1512,1516,1519,1527,1528,1529,1530,1537,1538,1539,1540,1546,1552,1566,1570,1572,1575,1578,1581,1582,1590,1593,1596,1605,1616,1617,1618,1621,1622,1625,1685,1687,1690,1693,1698,1705,1710,1716,1720,1721,1722,1730,1736,1745,1758,1776,1778,1787,1795,1813,1825,1827,1828,1829,1832,1834,1836,1837,1839,1840,1842,1847,1854,1856,1857,1858,1864,1865,1868,1871,1872,1877,1881,1882,1883,1884,1885,1901,1907,1908,1917,1921,1922,1932,1933,1934,1940,1946,1947,1949,1952,1953,1954,1956,1960,1961,1967,1968,1970,2006,2018,2026,2030,2032,2049,2050,2071,2091,2094,2138,2139,2150,2151,2154,2162,2172,2277,2320,2324,2341,2351,2369,2405,2414,2420,2421,2439,2442,2454,2475,2503,2519,2579,2593,2599,2616,2626,2637,2638,2647,2664,2666,2673,2677,2683,2687,2713,2727,2730,2735,2737,2741,2742,2743,2750,2769,2806,2833,2834,2850,2865,2875,2904,2905,2908,2915,2918,2941,2942,2947,2959,2971,2976,2998,3004,3008,3017)
group by `bug_id`
HAVING max(`date_modified`)
)
WHERE `bug_id` in (4,20,67,70,79,81,82,89,90,98,123,139,149,151,161,162,175,184,185,190,193,194,195,196,198,200,211,219,222,229,230,231,232,233,235,237,239,250,252,263,293,294,307,310,311,315,319,320,327,335,336,349,356,360,361,362,365,393,394,399,401,402,405,409,436,442,474,496,521,534,535,536,542,543,544,548,554,555,572,574,582,584,585,603,669,671,685,688,695,702,714,735,746,767,795,805,843,848,858,868,878,883,886,887,912,922,924,925,935,937,966,969,971,976,983,985,986,989,994,1001,1002,1005,1009,1021,1022,1023,1025,1037,1058,1062,1063,1076,1077,1089,1090,1093,1094,1101,1102,1110,1136,1150,1154,1156,1161,1162,1164,1168,1170,1176,1179,1180,1184,1189,1190,1194,1195,1199,1205,1206,1208,1210,1212,1214,1215,1220,1221,1227,1229,1230,1233,1235,1239,1251,1253,1257,1258,1264,1267,1268,1270,1271,1272,1274,1275,1276,1277,1283,1285,1289,1292,1295,1300,1309,1313,1317,1322,1323,1324,1326,1329,1330,1332,1333,1337,1339,1351,1352,1353,1363,1368,1371,1373,1376,1381,1382,1386,1387,1388,1389,1391,1398,1406,1417,1420,1422,1423,1424,1430,1438,1447,1455,1465,1469,1481,1485,1488,1490,1496,1502,1508,1511,1512,1516,1519,1527,1528,1529,1530,1537,1538,1539,1540,1546,1552,1566,1570,1572,1575,1578,1581,1582,1590,1593,1596,1605,1616,1617,1618,1621,1622,1625,1685,1687,1690,1693,1698,1705,1710,1716,1720,1721,1722,1730,1736,1745,1758,1776,1778,1787,1795,1813,1825,1827,1828,1829,1832,1834,1836,1837,1839,1840,1842,1847,1854,1856,1857,1858,1864,1865,1868,1871,1872,1877,1881,1882,1883,1884,1885,1901,1907,1908,1917,1921,1922,1932,1933,1934,1940,1946,1947,1949,1952,1953,1954,1956,1960,1961,1967,1968,1970,2006,2018,2026,2030,2032,2049,2050,2071,2091,2094,2138,2139,2150,2151,2154,2162,2172,2277,2320,2324,2341,2351,2369,2405,2414,2420,2421,2439,2442,2454,2475,2503,2519,2579,2593,2599,2616,2626,2637,2638,2647,2664,2666,2673,2677,2683,2687,2713,2727,2730,2735,2737,2741,2742,2743,2750,2769,2806,2833,2834,2850,2865,2875,2904,2905,2908,2915,2918,2941,2942,2947,2959,2971,2976,2998,3004,3008,3017)

Merci de me donner un coup de main.

Gôm

[Maljuna Kris]

Saluton,
Ça ne peut pas fonctionner, SET ___ = attend une valeur scalaire, pas tabulaire.

[CinePhil]

Bizarre le GROUP BY dans un UPDATE !

Je suppose que la liste des id ne sort pas d'un coup de dés ?
Si c'est le résultat d'une autre requête, pourquoi ne pas l'utiliser pour filtrer les lignes à mettre à jour ?

La structure des tables impliquées et leur colonne de jointure pourrait aussi nous aider à vous aider.

Une petite explication sur la nature de la modification et les critères d'obtention de la new_value serait utile.

[ced]

Bonjour,

Autre bizarrerie, le HAVING sans condition. Pourquoi ?
Normalement, la clause HAVING permet de mettre une condition sur une fonction d'agrégation. Or, dans le cas présent, il manque la condition...

Il nous faudrait plus de détail sur ce que vous souhaitez faire .

ced

[ghohm]

Merci à tous pour vos réponses, mais effectivement ma requête n'était pas des plus rigoureuses ...

Au final, je ne me suis pas pris la tête ...

J'ai fait ma sous-requête pour extraire les "new_value" en fonction de ma liste de "bug_id", puis j'ai dupliqué ma requête autant de fois que nécessaire ... dans Excel !!!

Explication : Sur chaque ligne, j'ai mis dans une colonne le début de ma requête, puis toutes mes "new_value" dans une colonne dédiée, le milieu de la requête, la liste des "bug_id" dans une colonne dédiée et enfin la fin de ma requête !


Gôm