update table1 a SET BZ='1' WHERE NOT exist (SELECT 1 FROM table2 b where a.id=b.id)
update table1 SET BZ='1' WHERE NOT EXISTS (SELECT id FROM table2 where table1.id=table2.id);为id列创建索引
update table1 SET BZ='1' WHERE id= (select a.id from table1 a minus select b.id from table2 b)
楼主的SQL会很慢,因为你的SQL做了一个笛卡儿乘积。 使用Not exists会快一点。update table1 SET BZ='1' WHERE NOT EXISTS(select 1 from table2 where id = table1.id)
update table1 a SET BZ='1' WHERE NOT exists (SELECT 1 FROM table2 b where a.id=b.id)
qiyousyc(qiyou_syc) 的办法应该可以,他用的是集合的差
to snowy_howe(天下有雪) 试过了~~~呵呵~~~是快了~~to jdcl2000(绝地苍狼)
理论上用哪个快一点(not exixts or minus )??
如果是oracle: SQL:update table1 SET BZ='1' WHERE id NOT IN (SELECT id FROM table2) 改为: update table1 set bz='1' where id in ( select id from table1 minus select id from table2 )
SET BZ='1'
WHERE NOT exist (SELECT 1 FROM table2 b where a.id=b.id)
(select a.id from table1 a minus select b.id from table2 b)
使用Not exists会快一点。update table1 SET BZ='1'
WHERE NOT EXISTS(select 1 from table2 where id = table1.id)
SET BZ='1'
WHERE NOT exists (SELECT 1 FROM table2 b where a.id=b.id)
的办法应该可以,他用的是集合的差
理论上用哪个快一点(not exixts or minus )??
SQL:update table1 SET BZ='1' WHERE id NOT IN (SELECT id FROM table2)
改为:
update table1 set bz='1'
where id in
(
select id from table1
minus
select id from table2
)