就是比如我一个表里面有 name 字段,里面有10000数据,另一个表里面也有name字段,有100个数据,把前者表里面有后者表字段的数据记录都删除
是这样吗? create table t3 (name varchar(20)) insert into t3 select '张三' union all select '李四' union all select '王五' union all select '马六'create table t4 (name varchar(20)) insert into t4 select '张三' union all select '李四' ---------------- delete t3 where t3.name in (select name from t4 )
delete from table_1 where name in (select NAME from table_2)
delete from table_1 where name in (select NAME from table_2) 楼上正解,但如果表2数据量大,用IN效率不太好
delete from table_1 where name in (select NAME from table_2) 楼上正解,但如果表2数据量大,用IN效率不太好不用IN那应该怎么写呢?
delete from table_1 where name in (select NAME from table_2) 不用IN用exists delete from table_1 where exists (select * from table_2 where table_2.name=table_1.name)
這裡用Exists還沒有用in的效率高 .
delete from table_1 where name in (select distinct NAME from table_2)
delete table_1 from table_1 a inner join table_2 b on a.name = b.name
create table t3 (name varchar(20))
insert into t3
select '张三'
union all
select '李四'
union all
select '王五'
union all
select '马六'create table t4 (name varchar(20))
insert into t4
select '张三'
union all
select '李四'
----------------
delete t3
where t3.name in
(select name from t4 )
where name in (select NAME
from table_2)
where name in (select NAME
from table_2)
楼上正解,但如果表2数据量大,用IN效率不太好
where name in (select NAME
from table_2)
楼上正解,但如果表2数据量大,用IN效率不太好不用IN那应该怎么写呢?
where name in (select NAME
from table_2)
不用IN用exists
delete from table_1
where exists (select * from table_2 where table_2.name=table_1.name)