求解SQL语句

[wq58]

假设有一主表 Table(TTID,IFOVER)
从表 DetailTable(DETAILID,TTID,...,IFOVER)

Table: DetailTable:

TTID IFOVER DetailID, TTID,..IFOVER
1001 0 1 1001 0
2 1001 1

通过TTID字段连接，IFOVER字段为逻辑值(0,1)，要实现当DetailTable表中的IFOVER
值全部为1时将Table表的IFOVER值赋为1.谢谢

[天剑68]

update table set ifover=1 where ttid='你想更新的ttid' and ttid not in
(select ttid from detailtable where ifover=0 and ttid='你想更新的ttid')

[coolingwind]

update table set ifover=1 where ttid in (select ttid from detailtable where ifover=1)

[yujohny]

update A
set A.IFOVER=CASE isnull(B.count,0)<0 then 1 else 0 end
from Table A left join
(select TTID,count(*) as count from DetailTable
where IFOVER=0 group by TTID) B ON B.TTID=A.TTID

[yujohny]

--笔误：
update A
set A.IFOVER=CASE when isnull(B.count,0)<0 then 1 else 0 end
from Table A left join
(select TTID,count(*) as count from DetailTable
where IFOVER=0 group by TTID) B ON B.TTID=A.TTID

[coolingwind]

update table set ifover=1 where ttid in ((select count(ttid) from detailtable where ifover=1)=((select count(ttid) from detailtable ))

[gmlxf]

update table set IFOVER=1 where (select min(IFOVER) from DetailTable where TTID=a.TTID)=1 from Table a

[yujohny]

--或者用这句
update [table]
set IFOVER=1
where not exists(select 1 from DetailTable where TTID=[table].TTID and IFOVER=0)


不过两者有点区别，第一句会将没有明细的主记录更新为零，后面这一句会更新为1

[coolingwind]

update A
set A.IFOVER=CASE when isnull(B.count,0)<0 then 1 else 0 end
from Table A,detailtable b left join
(select TTID,count(*) as count from DetailTable
where IFOVER=0 group by TTID) B ON B.TTID=A.TTID

[gmlxf]

写错了
update table set IFOVER=1 from Table a
where (select min(IFOVER) from DetailTable where TTID=a.TTID)=1