如何使用来自不同表的数据按条件删除行?

时间:2021-10-03 19:19:43

I have a table1:

我有一个table1:

ID month  first_name last_name
11  1       bla1       bla2
11  2       bla1       bla2
11  3       bla1       bla2
11  4       bla1       bla2
22  1       bla3       bla4
22  2       bla3       bla4
22  3       bla3       bla4
22  4       bla3       bla4

And another table - table2:

另一个表 - table2:

Id month
11  1
11  3 
22  4 

And I want to delete the records from table 1 according to table 2 so I end up with:

我想根据表2删除表1中的记录,所以我最终得到:

ID month  first_name last_name

    11  2       bla1       bla2
    11  4       bla1       bla2
    22  1       bla3       bla4
    22  2       bla3       bla4
    22  3       bla3       bla4

Any simple way to do that?

有什么简单的方法吗?

Delete from table1 tb1
where tb1.Id = tb2.Id and tb1.month  = tb2.month

but I'm sure about the correct join.

但我确定正确的加入。

2 个解决方案

#1


4  

You can do something like this to specify what data to delete when doing a join.

您可以执行类似的操作来指定在执行连接时要删除的数据。

DELETE tb1
FROM table1 as tb1
INNER JOIN table2 as tb2 ON tb1.Id = tb2.Id
WHERE tb1.month = tb2.month

#2


0  

delete tb1 
from table1 tb1
where exists
         (select 1 from table2 
           where tb1.id=tb2.id 
          and tb1.month=tb2=month)

#1


4  

You can do something like this to specify what data to delete when doing a join.

您可以执行类似的操作来指定在执行连接时要删除的数据。

DELETE tb1
FROM table1 as tb1
INNER JOIN table2 as tb2 ON tb1.Id = tb2.Id
WHERE tb1.month = tb2.month

#2


0  

delete tb1 
from table1 tb1
where exists
         (select 1 from table2 
           where tb1.id=tb2.id 
          and tb1.month=tb2=month)