sql批量更新update嵌套select更新

灰太狼 2022-10-16 08:45 240阅读 0赞

## 概述 ##

> 有两张表【**user**】和【**city**】，user表的 `city_uuid`、`city_no` 和 city 表的 `city_uuid`、`city_no` 一一对应，但是 **user** 表只有 `city_uuid`，这时候需要将 **city** 对应的 `city_no` 批量更新到 **user** 表中  
> ![在这里插入图片描述][20210528163502710.png]  
> ![在这里插入图片描述][20210528163519395.png]

## 批量更新方式 ##

第一种方式（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
    set t1.column= ?, t2.column, t3.column = ?, ... , tn.column = ?
    where t1.xx= ?, t2.xx = ?, ... , tn.xx = ?

案例：（conditionUuid是user表的外键，每个conditionUuid对应两条user记录，将producter记录覆盖consumer记录的指定字段值)

update r2
    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
    on r1.conditionUuid = r2.conditionUuid
    where r1.conditionValue = 'condition-consumer-00000000000000000' and r1.userName is not null
    and r2.conditionValue = 'condition-producter-0000000000000000' and r2.userName is not null

[20210528163502710.png]: /images/20221014/5b32e0bc3892426e889e1172de222ef6.png
[20210528163519395.png]: /images/20221014/cf06e8184443413abf742313a3fbd19a.png