要实现下面的功能,SQL语句怎么写?比如:
Name Result
-------------------------
AAA Yes
BBB Yes
AAA
BBB
CCC Yes
AAA Yes
......Name相同时,如果有一个是Yes,都要改成Yes即:AAA和BBB的都更新为Yes
Name Result
-------------------------
AAA Yes
BBB Yes
AAA
BBB
CCC Yes
AAA Yes
......Name相同时,如果有一个是Yes,都要改成Yes即:AAA和BBB的都更新为Yes
set result = 'yes'
from tb
where name in (select distinct name from tb where result = 'yes')
set result='yes'
where exists(select 1 from tb t2 where tb.name=t2.name and result='yes')
set result = 'yes'
from tb
where name in (select distinct name from tb where result = 'yes')
insert into tb values('AAA', 'Yes')
insert into tb values('BBB', 'Yes')
insert into tb values('AAA', '')
insert into tb values('BBB', '')
insert into tb values('CCC', 'Yes')
insert into tb values('AAA', 'Yes')
goupdate tb
set result = 'yes'
from tb
where name in (select distinct name from tb where result = 'yes')select * from tbdrop table tb/*
Name Result
---------- ----------
AAA yes
BBB yes
AAA yes
BBB yes
CCC yes
AAA yes(所影响的行数为 6 行)
*/
update tb set result='yes' from tb a,(select name from tb where result='yes') b
where a.name=b.name