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