Mysql从另一个表中删除一个表中的id [英] Mysql delete in one table by id's from another table

问题描述

我有两张桌子:

A身份证 |名称

和

B身份证 |名称

在 B 表上，我创建查询以选择所有数据，其中 id 位于(...一些值)，但是我如何在一个查询中选择此 B - id 并从 A 中删除，其中 A.id = B.id?

我是 sql 的新手.... 找不到如何在一个查询中组合所有...

*i must select from b on some like query，例如我的 b 查询: select * from B where somefieldinteger in (1,4,9,21,25) and that b.id must compare with a*

更新这个抛出错误

 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 (`.ART_SUP_ID (10008,10439,11005,10097,10669,11100,80,10912,10683,10675,10194,11196,1166,10730,10209,10,10,10,108,10,108,10,108,10,108,10,108,10,14,108,108，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,1010,1010,1010,10,10,10,10,10,10,10,10,10,10,10,10,107,107,107,107,1077,10714,10224,750,10267,10179,10725,10774,11063,10868,10103,10676,10057,10649,255,10322,11022,101010,1010,1010,1010,1010,1010,1018101810,381,10781,10699,11120,11126,830,10240,11162,10436,10584,10342,10861,11190,10721,11171,10564,610,1070,1040,1040,1040,104,1070,40,107,107,104,10721,10721,10564,1040,10705,444,426,10059,153,122,10674,64,113,11101,10231,10337,806,11117,10385,251,11188,491,11192,100,1092,10069,10864,11099,10246,10178,10758,10568,10230,10124,10384,10782,10726,384,10670,305,10763,105,10763,108,10763,108,10763,108,10763,108,10763,107,108,1078510814,10582,10382,11093,11173,10381,427,441)) 限制 50

解决方案

使用 delete 和 join 像这样:

mysql>创建表 a (id int);mysql>插入值 (1), (2), (3), (4);mysql>创建表 b (id int);mysql>插入 b 值 (2), (3);mysql>从 a.id=b.id 上的连接 b 中删除 a，其中 b.id >2;mysql>从 a 中选择 *；+------+|身份证 |+------+|1 ||2 ||4 |+------+

这可以扩展到任意数量的表，例如:

删除一个从一个加入 b 上 a.idA=b.idAJOIN c ON b.idB=c.idB;

这将从 a 中删除所有从 b 的记录中引用的记录，而这些记录又从 c 中引用.你也可以像这样添加 WHERE 子句:

删除一个从一个加入 b 上 a.idA=b.idA加入 c on b.idB=c.idB哪里 a.status='done' ANDb.status='open' ANDc.status='打开';

如果您想限制要删除的行数，请执行以下操作:

删除一个从一个加入 b 上 a.idA=b.idA加入 c on b.idB=c.idB限制 500000；

如果你想删除前500000行，你需要细化哪些行在前，所以你需要在行之间建立一些排序.换句话说，您需要按某些条件对行进行排序，然后像这样限制:

删除一个从一个加入 b 上 a.idA=b.idA加入 c on b.idB=c.idB按某物订购限制 500000；

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

解决方案

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;

这篇关于Mysql从另一个表中删除一个表中的id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！