最近在进行2000向2005转变的学习,遇到一个exists使用的问题.
在删除重复行中(有唯一ID)exists的使用和in的使用效果相同,但是在更新语句上
exists和in的效果就不同了,觉得奇怪.如下是测试代码
删除重复行的
if object_id('table2')is not null drop table table2
create table table2(id int identity(1,1) primary key, name varchar(2))
insert into table2(name)
select 'a' union all
select 'a' union all
select 'b' union all
select 'c' union all
select 'c' union all
select 'd' select * from table2delete table2 from table2 a where exists (select 1 from table2 where name=a.name and id<a.id)delete table2 from table2 a where name in (select name from table2 where name=a.name and id<a.id)更新行的
if object_id('tba')is not null drop table tba
if object_id('tbb')is not null drop table tbb
create table tba(c1 varchar(10),c2 varchar(10),c3 int)
insert tba select
'str1 ','str2', null union select
'str3 ','str4', null union select
'str5 ','str6', null union select
'str7 ','str8', null create table tbb(c1 varchar(10),c2 varchar(10))
insert tbb select
'str7 ','str8' union select
'str1 ','str3' union select
'str11 ','str22' union select
'str3 ','str4'
go
update tba
set c3=1
where c1 in(select tba.c1 from tba,tbb where tba.c1=tbb.c1 and tba.c2=tbb.c2)update tba
set c3=1
where exists(select 1 from tba,tbb where tba.c1=tbb.c1 and tba.c2=tbb.c2)select * from tba
在删除重复行中(有唯一ID)exists的使用和in的使用效果相同,但是在更新语句上
exists和in的效果就不同了,觉得奇怪.如下是测试代码
删除重复行的
if object_id('table2')is not null drop table table2
create table table2(id int identity(1,1) primary key, name varchar(2))
insert into table2(name)
select 'a' union all
select 'a' union all
select 'b' union all
select 'c' union all
select 'c' union all
select 'd' select * from table2delete table2 from table2 a where exists (select 1 from table2 where name=a.name and id<a.id)delete table2 from table2 a where name in (select name from table2 where name=a.name and id<a.id)更新行的
if object_id('tba')is not null drop table tba
if object_id('tbb')is not null drop table tbb
create table tba(c1 varchar(10),c2 varchar(10),c3 int)
insert tba select
'str1 ','str2', null union select
'str3 ','str4', null union select
'str5 ','str6', null union select
'str7 ','str8', null create table tbb(c1 varchar(10),c2 varchar(10))
insert tbb select
'str7 ','str8' union select
'str1 ','str3' union select
'str11 ','str22' union select
'str3 ','str4'
go
update tba
set c3=1
where c1 in(select tba.c1 from tba,tbb where tba.c1=tbb.c1 and tba.c2=tbb.c2)update tba
set c3=1
where exists(select 1 from tba,tbb where tba.c1=tbb.c1 and tba.c2=tbb.c2)select * from tba
set c3=1
where exists(select 1 from tbb where tba.c1=tbb.c1 and tba.c2=tbb.c2)
set c3=1
where exists(select 1 from tba,tbb where tba.c1=tbb.c1 and tba.c2=tbb.c2)----------------
update tba
set c3=1
where exists(select 1 from tbb where tba.c1=tbb.c1 and tba.c2=tbb.c2)
if object_id('tbb')is not null drop table tbb
create table tba(c1 varchar(10),c2 varchar(10),c3 int)
insert tba select
'str1 ','str2', null union select
'str3 ','str4', null union select
'str5 ','str6', null union select
'str7 ','str8', null create table tbb(c1 varchar(10),c2 varchar(10))
insert tbb select
'str7 ','str8' union select
'str1 ','str3' union select
'str11 ','str22' union select
'str3 ','str4'
go--search
select m.c1 , m.c2 , c3 = case when exists (select 1 from tbb n where c1 = m.c1 and c2 = m.c2) then 1 end from tba m
/*
c1 c2 c3
---------- ---------- -----------
str1 str2 NULL
str3 str4 1
str5 str6 NULL
str7 str8 1(所影响的行数为 4 行)
*/--update
update tba set c3 = case when exists (select 1 from tbb n where c1 = m.c1 and c2 = m.c2) then 1 end from tba m
select * from tba/*
c1 c2 c3
---------- ---------- -----------
str1 str2 NULL
str3 str4 1
str5 str6 NULL
str7 str8 1(所影响的行数为 4 行)
*/
update tba <==此tba 与下面的tba含义不同
set c3=1
where exists(select 1 from tba,tbb where tba.c1=tbb.c1 and tba.c2=tbb.c2)
~~~你的语句的意思是 只要select 1 from tba,tbb where tba.c1=tbb.c1 and tba.c2=tbb.c2存在记录,就更新tba表
很显然你的更新表与你的判断之没有关系呀update tba
set c = 1
where exists(select 1)
条件永远成立全更新掉了