delete from 表2 where username not in (select username from 表1)
delete from 表2 where ID not exists(select B.ID from 表1 A,表2 B where A.username=B.username and A.tel=B.tel and A.address=B.address) 试试吧!
delete from 表2 a where exists(select username,tel,address from 表1 b where a.username=b.username, and a.tel=b.tel, and a.address=b.address)
delete from 表2 where not exists(select * from 表1 where username=表2.username and tel=表2.tel and address=表2.address)
刪除表2中存在 在表1中不存在的數據 delete from 表2 where not exists(select * from 表1 where username=表2.username and tel=表2.tel and address=表2.address)
一样的delete 表2 from 表2 left join 表1 on 表1.username=表2.username and 表1.tel=表2.tel and 表1.address=表2.address where 表1.tel is null
如果楼主表中的ID是IDENTITY的缩写,且是以username 为主键,那么该语句应该是: delect from 表2 where username not in(select username from 表1 ) 否则还要看具体情况而定!楼主可把这些细节说明一下! 如果对两表有进行insert/update/delete操作的话还牵涉到用触发器。
没有括号的写法 delete 表2 from 表2 left join 表1 on 表1.username=表2.username and 表1.tel=表2.tel and 表1.address=表2.address where 表1.tel is null
如果楼主无补充,就只能按无主键处理,那么如下方法绝对正确: delete from 表2 where not exists(select * from 表1 where username=表2.username and tel=表2.tel and address=表2.address)
试试吧!
where exists(select username,tel,address
from 表1 b
where a.username=b.username,
and a.tel=b.tel,
and a.address=b.address)
and tel=表2.tel and address=表2.address)
delete from 表2 where not exists(select * from 表1
where username=表2.username and tel=表2.tel and address=表2.address)
表1.username=表2.username and
表1.tel=表2.tel and
表1.address=表2.address
where 表1.tel is null
delect from 表2 where username not in(select username from 表1 )
否则还要看具体情况而定!楼主可把这些细节说明一下!
如果对两表有进行insert/update/delete操作的话还牵涉到用触发器。
delete 表2 from 表2 left join 表1 on
表1.username=表2.username and
表1.tel=表2.tel and
表1.address=表2.address
where 表1.tel is null
delete from 表2 where not exists(select * from 表1 where username=表2.username and tel=表2.tel and address=表2.address)