Valdis Azamaris Valdis Azamaris - 2 months ago 17
MySQL Question

Mysql delete with join and limit

I have this query:

DELETE `LINK_LA_TYP`
FROM `LINK_LA_TYP`
JOIN `LINK_ART` ON `LINK_LA_TYP`.LAT_LA_ID = `LINK_ART`.LA_ID
JOIN `ARTICLES` ON `LINK_ART`.LA_ART_ID = `ARTICLES`.ART_ID
WHERE (`ARTICLES`.ART_SUP_ID in (10008,10439,11005,10097,10669,11100,80,10912,10683,10675,10194,11196,1166,10730,10248,10870,11200,11059,247,10121,10911,489,10724,496,10093,10205,1318,10953,11199,11047,128,114,194,10865,11058,10345,1286,10667,10064,11077,10622,11205,10917,10344,495,10709,10954,10744,304,10957,10447,10764,10129,10862,10918,10731,11115,10095,10859,10580,1345,10177,10323,144,11182,10132,256,10941,58,10006,10017,10780,10765,10665,11110,10714,10224,750,10267,10179,10725,10774,11063,10868,10103,10676,10057,10649,255,10322,11022,309,10754,11121,10801,10018,11004,10245,146,11056,381,10781,10699,11120,11126,830,10240,11162,10436,10584,10342,10861,11190,10721,11171,10564,10545,94,10087,73,10755,10869,10547,10706,10346,444,426,10059,153,122,10674,64,113,11101,10231,10337,806,11117,10385,251,11188,491,11192,100,10792,10069,10864,11099,10246,10178,10758,10568,10230,10124,10384,10782,10726,384,10670,305,10763,10768,10585,10394,10552,498,10677,1348,168,10814,10582,10382,11093,11173,10381,427,441)) limit 50000;


but why do I get an error?


1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'limit 50' at line 1


What's wrong? And how to limit delete query entries to delete?

Answer

From the manual:

For the multiple-table syntax, DELETE deletes from each tbl_name the rows that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used.

You are better off SELECTing the ids you want to delete with said limit and then deleting those.

Comments