Question

SQL语句.删除两张表相同的数据

删除A表（字段:bm,cm）,B表(字段:bm,cm)两表直接数据相同的所有数据，至少两种解决方案

Answer 1

方法1：
delete from A t1
where exists
( select 1 from B t2
where t1.bm = t2.bm
and t2.cm = t2.cm
) ;
commit;

delete from B t1
where exists
( select 1 from A t2
where t1.bm = t2.bm
and t2.cm = t2.cm
) ;
commit;

方法二：

delete from A t1
where to_char(t1.bm)||to_char(t1.cm)
in (select to_char(t2.bm)||to_char(t2.cm) from B t2);

commit;

delete from B t1
where to_char(t1.bm)||to_char(t1.cm)
in (select to_char(t2.bm)||to_char(t2.cm) from A t2);

commit;

to_char() 是转成字符串的函数，防止bm,cm是数字。
如果bm,cm都是字符，可以省略to_char().
以上oracle 适用。