多表更新update

多表根据条件进行更新操作

1.(通用)

update 表名 set 属性 = 值 where 属性 in ( select 属性 from 表名 where 条件)

实例:

update business_purchase_order_mx
set status = '已发货'
where id in (
select purchasehid
from business_purchase_delivery_mx
where status != '待发货'
)

2.(SqlServer写法)

update 表名1 set 属性 = 值 from 表名1,表名2 where 属性1 = 属性2

实例:

update a set a.status = '已发货' 
from business_purchase_order_mx a,business_purchase_delivery_mx b
where a.id = b.purchasehid and b.status ! = '待发货'

3.(MySQL写法)

update 表名1,表名2 set 属性 = 值 where 属性1 =属性2

实例:

update business_purchase_order_mx a,business_purchase_delivery_mx b

set a.status = '已发货' 
where a.id = b.purchasehid and b.status ! = '待发货'

补充:

SQLServer多表更新方法：

语法: