如果不在表1中，则根据2个字段从表2中删除 - remove from table 2 if not in table 1, based on 2 fields

remove from table 2 if not in table 1, based on 2 fields

本文关键字：2个字段删除如果不 | 更新日期: 2023-09-27

它们都有fileId和userId字段

表1：fileId userId

表2：fileId userId

如果不在表1中，我想根据它们的fileId和userId从表2中删除所有行。。不仅仅是一个领域，而是两个领域。。。

谨致问候，J

delete from table2 
where fileid not in (select fileid from table1)
and userId not in (select userId from table1)

TRY（假设id是两个表的主键）

DELETE FROM table2
WHERE id NOT IN ( 
        SELECT 'id' FROM table1 t1 
        INNER JOIN table2 t2 ON ( t1.field=t2.filed AND t1.userid = t2.userid)
        )

DELETE FROM table2 WHERE userId NOT IN (SELECT userId from table1) AND 
                         fileId NOT IN (SELECT fileId from table1)

看看这种方法。测试工作。

CREATE table table1(field varchar(10),userid varchar(20));
CREATE table table2(field varchar(10),userid varchar(20));
insert into table1 values('10','vish');
insert into table1 values('11','atul');
insert into table1 values('12','nish');
insert into table2 values('10','vish');
insert into table2 values('11','atul');
insert into table2 values('13','paul');
insert into table2 values('14','ganesh');
DELETE from table2
WHERE NOT exists 
(select field+userid from table1 t1 where t1.field+userid = table2.field+userid);
SELECT * FROM table2;