T1表 结构

id????? name?????? school

1???????ming1????? 清华大学

2?????? ming2?????? 北京大学

3?????? ming3?????? 复旦大学

?

T2表? 结构

id????? student??? school

11???? ming1????? 牛津大学

12???? ming3?????? 剑桥大学

?

T1表的name 和 T2表的 student? 值是 1对1 的对应。

需求：这两个字段的值相同时，根据B表的school更新A表的school。

语句：

update t1? set school = (select school from t2 where name=student) where? exists (select * from t2 as b where name = b.student);

结果：

?

T1表 结构

id????? name?????? school

1???????ming1????? 牛津大学

2?????? ming2?????? 北京大学

3?????? ming3?????? 剑桥大学

?

注意：

条件里不要再加本身要更新的表,

?

PS:

关联查询语句：

select aii.update_time,pn.public_time,pn.* from t_apply_item_instance aii, t_public_notice pn where pn.apply_item_instance_uuid = aii.uuid and
?aii.apply_item_uuid in ('0101','0102','0103','0104','0105','0106','0201','0202','0203','0204','0205','0206') and aii.apply_status = 21
?and pn.public_type = 1

?

更新语句：

update t_public_notice set public_time =
(select aii.update_time from t_apply_item_instance aii where apply_item_instance_uuid = aii.uuid and
?aii.apply_item_uuid in ('0101','0102','0103','0104','0105','0106','0201','0202','0203','0204','0205','0206') and aii.apply_status = 21
?and public_type = 1 )
?where exists
?(select t1.* from t_apply_item_instance as t1 where apply_item_instance_uuid = t1.uuid and
?t1.apply_item_uuid in ('0101','0102','0103','0104','0105','0106','0201','0202','0203','0204','0205','0206') and t1.apply_status = 21
?and public_type = 1)

?

update的表在后面的条件里面是没有了的

?