sqlÎÊÌâ¡£ - Oracle / »ù´¡ºÍ¹ÜÀí

Ö÷±í A ÖУ¬¶ÔÓ¦B±íÓжàÌõÊý¾Ý ͨ¹ýB±íµÄmid ÓëA±í¹ØÁª¡£B±íÓиö×Ö¶Îstate£¬Ö»ÓÐstateȫΪ1ʱ²Å½«A±íÖеÄstate ÉèΪ1.
½á¹¹»ù±¾ÈçÏ£º
A
ID state 
1 0


B
ID MID state
1 1 1
2 1 1 
3 1 1


×îºóµÄ½á¹ûÐèÒª¡£
AID Astate
1 1


Ò»Ìõsql¿ÉÒÔʵÏÖÂð

---

SQL code:


create table a (id int,state int)
insert into a values(1,0)

create table b (id int,mid int,state int)
insert into b values(1,1,1)
insert into b values(2,1,1)
insert into b values(3,1,1)


update a set state=1
from a
where exists(select 1 from b where a.id=b.mid and state=1)

select * from a
id state
1 1

---

Ŷ¡£ÎÒÐèÒªÒ»Ìõ²éѯsql£¬£¬a±íÖеÄÖµ²»±ØÐ޸ġ£

---

SQL code:
UPDATE a
SET a.state =
(SELECT MIN(nvl(b.state, 0)) from b WHERE b.mid = a.id)
WHERE EXISTS (SELECT 1 from b WHERE b.mid = a.id);

---

SQL code:
--»òÕßÕâ¸ö
UPDATE a
SET a.state =
(SELECT case when COUNT(1)>0 then 0 else 1 end
from b
WHERE b.mid = a.id AND
a.id != 1)
WHER