关于exists的疑问

[chenxin2835]

最近在进行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 table2

delete  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

[chenxin2835]

含泪期待ING

[-狙击手-]

update tba
set c3=1
where exists(select 1 from tbb where tba.c1=tbb.c1 and tba.c2=tbb.c2)

[chenxin2835]

昨晚没睡好,大清早的就迷迷糊糊,想睡觉

[-狙击手-]

update tba
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)

[chenxin2835]

哦?是子查询中的表连接造成的?

[chenxin2835]

谢谢狙击手,能否给我解释一下呢

[dawugui]

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

--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 行）
*/

[xiaoliaoyun]

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表

[-狙击手-]

[Quote=引用 6 楼 chenxin2835 的回复:]
谢谢狙击手,能否给我解释一下呢
[/Quote]

很显然你的更新表与你的判断之没有关系呀

update tba
set c = 1
where exists(select 1)
条件永远成立

全更新掉了

[chenxin2835]

噢,原来这样啊,嘿嘿,谢谢各位了

[正宗老冉]

纯属友情散分！