Mysql delete in one table by id’s from another table

MySQLsql

I have two tables:

A
id | name

and

B
id | name

On B table i create query to select all data, where id's are in (… some values), but how can i in one query select this B – id's and delete from A where A.id = B.id ?

I'm new to sql…. Could not find how to combine all in one query…

*i must select from b on some like query, for example my b query: select * from B where somefieldinteger in (1,4,9,21,25) and that b.id must compare with a*

upd
this throw error

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 50

Best Answer

Use delete together with join like this:

mysql> create table a (id int);
mysql> insert into a values (1), (2), (3), (4);
mysql> create table b (id int);
mysql> insert into b values (2), (3);
mysql> delete a from a join b on a.id=b.id where b.id > 2;
mysql> select * from a;
+------+
| id   |
+------+
|    1 |
|    2 |
|    4 |
+------+

This scales to arbitrary number of tables, e.g.:

DELETE a 
FROM a 
JOIN b ON a.idA=b.idA 
JOIN c ON b.idB=c.idB;

This will delete from a all records that are referred from those records of b which are in turn referred from c. You may also add WHERE clause like this:

DELETE a 
FROM a 
JOIN b ON a.idA=b.idA 
JOIN c ON b.idB=c.idB
WHERE a.status='done' AND
      b.status='open' AND
      c.status='open';

If you want to limit number of rows to be deleted, do like this:

DELETE a 
FROM a 
JOIN b ON a.idA=b.idA 
JOIN c ON b.idB=c.idB
LIMIT 500000;

If you want to delete first 500000 rows, you need to refine which rows are first, so you need to establish some ordering among rows. In other words you need to sort rows by some criteria and then limit like this:

DELETE a 
FROM a 
JOIN b ON a.idA=b.idA 
JOIN c ON b.idB=c.idB
ORDER BY a.something
LIMIT 500000;
Related Topic