sql批量更新update嵌套select更新
概述
有两张表【user】和【city】，user表的 city_uuid、city_no 和 city 表的 city_uuid、city_no ⼀⼀对应，但是 user 表只有
city_uuid，这时候需要将 city 对应的 city_no 批量更新到 user 表中
批量更新⽅式
第⼀种⽅式（inner join 内连接）
update u
set u.city_no = c.city_no
from user u
inner join city c
on u.city_uuid = c.city_uuid
where u.city_uuid is not null and u.city_no is null
第⼆种⽅式（⼦查询）
update u
set u.city_no =(select c.city_no from city c where u.city_uuid = c.city_uuid)
from user u
第三种⽅式：（笛卡尔积）
update u
set u.city_no = c.city_no
from[user] u, city c
where u.city_uuid = c.city_uuid
update 多表更新
update table1 t1,table2 t2, table3 t3,..., tablen tn
lumn= ?, t2.column, t3.column= ?,..., tn.column= ?
= ?, t2.xx = ?,..., tn.xx = ?
案例：（conditionUuid是user表的外键，每个conditionUuid对应两条user记录，将producter记录覆盖consumer记录的指定字段值)
update r2批量更新sql语句
set r2.userUuid = r1.userUuid, r2.userName = r1.userName , r2.age = r1.age, r2.updatedTime ='2021-02-22 22:22:22.222'
from user r1
inner join user r2
ditionUuid = r2.conditionUuid
ditionValue ='condition-consumer-00000000000000000'and r1.userName is not null
ditionValue ='condition-producter-0000000000000000'and r2.userName is not null

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。