SQL --update批量更新

2019独角兽企业重金招聘Python工程师标准>>>

需求：将t_user_info表中user_img、nickname、user_bg_img三个字段的值设置成与t_hr表中的hr_name、hr_img、hr_bg_img三个字段的值一样。

t_user_info表如下

t_hr表如下

t_user_info表数据存放情况如下

t_hr表数据存放情况

由两张数据表存放数据情况可以看出，t_user_info表里存放的是用户信息，id从1000000000开始，依次推增。t_hr表里存放的是hr信息，他们的关系是：用户= 普通用户+HR用户。

需求分析：

工程开始的时候，t_user_info表里的user_img数据与t_hr表里hr_img数据并没有保持一致。现如今要求设置成一致。

即需要批量更新t_user_info表里user_img、user_bg_img和nickname字段。

最开始，我写的sql如下，(这里为了方便起见，先更新一个字段试下)

UPDATE t_user_info tui
SET tui.USER_IMG = (
    SELECT
        th.HR_IMG
    FROM
        t_hr th
    WHERE
        th.USE_FLAG = 1
    AND 
        th.AUDIT_FLAG = 1
    AND tui.USER_ID = th.HR_ID
)

接着，我验证我写的sql是不是对的，查看数据库，结果发现：

1.t_hr表中hr_id 等于 t_user_info表中user_id的数据都是对的

2.两个id不相等的数据，都被赋值成 空数据了，图如下

更新之前，我将t_user_info表里面user_img的数据全部设置成1

上述sql更新之后的图如下

很明显user_id在t_hr表中存在的数据都更新成功了，例如：1000000000、1000000001、1000000004、……等。

当user_id不在t_hr表中的数据，例如1000000002、1000000003、1000000005、……等，就出现了错误，原本的数据为1，更新后变成空了。

接着开始找原因

很明显，上述的跟新是将t_user_info表中所有的数据都更新了，我们的需求是仅仅只需要更新user_id=hr_id的数据，user_id原本的数据还需要保留。因此，想到了在更新语句后面加条件，SQL如下：

很明显看到上述sql报错了，找不到th.HR_ID这列。

我就在这个带条件的地方卡了很久

最终在网上查找资料时，才发现updatre语句后面，可以跟多张表，接着将SQL改成如下

执行语句成功了，这步很关键。但是发现执行后message里面的信息，发现受影响的行数为35。

接着来查找满足条件的总个数是不是35，执行如下SQL

发现是25

对比查询where后的条件发现，原来是更新语句的条件带少了，马上补上去，SQL如下

恩恩，这就对了，接着我补上了USER_BG_IMG、NICKNAME，SQL如下

UPDATE t_user_info tui,t_hr th
SET tui.USER_IMG = (
    SELECT
        th.HR_IMG
    FROM
        t_hr th
    WHERE
        th.USE_FLAG = 1
    AND 
        th.AUDIT_FLAG = 1
    AND tui.USER_ID = th.HR_ID
),
tui.USER_BG_IMG = (
    SELECT
        th.HR_BG_IMG
    FROM
        t_hr th
    WHERE
        th.USE_FLAG = 1
    AND 
        th.AUDIT_FLAG = 1
    AND tui.USER_ID = th.HR_ID
),
tui.NICKNAME = (
    SELECT
        th.HR_NAME
    FROM
        t_hr th
    WHERE
        th.USE_FLAG = 1
    AND 
        th.AUDIT_FLAG = 1
    AND tui.USER_ID = th.HR_ID
)
    WHERE
        th.USE_FLAG = 1
    AND 
        th.AUDIT_FLAG = 1
    AND tui.USER_ID = th.HR_ID

接着很开心的交了任务。不一会儿，项目经理看到这段代码了，立马改写了SQL，如下

UPDATE t_user_info tui,t_hr th
SET tui.user_img = th.hr_img,
 tui.user_bg_img = th.hr_bg_img,
 tui.NICKNAME = th.HR_NAME
WHERE
    tui.user_id = th.hr_id
AND th.USE_FLAG = 1
AND th.AUDIT_FLAG = 1

其实，仔细看了下SQL，优化后还是对的。

转载于:https://my.oschina.net/u/2312022/blog/617269