时间戳

首页 > 解决方案 > 更新数据库不影响不同的情况

sql - 更新数据库不影响不同的情况

问题描述

我有一个问题，我想为不同的情况创建一个条件，例如：

当（WeekDay == 2）字段 CanWork2 应该是 1 但这不会发生只是更改 1 字段“CanWork1”

UPDATE
    Emps
SET
    CanWork1 = CASE WHEN cd.WeekDay = 1 THEN 1 ELSE CanWork1 END,
    CanWork2 = CASE WHEN cd.WeekDay = 2 THEN 1 ELSE CanWork2 END,
    CanWork3 = CASE WHEN cd.WeekDay = 3 THEN 1 ELSE CanWork3 END,
    CanWork4 = CASE WHEN cd.WeekDay = 4 THEN 1 ELSE CanWork4 END,
    CanWork5 = CASE WHEN cd.WeekDay = 5 THEN 1 ELSE CanWork5 END,
    CanWork6 = CASE WHEN cd.WeekDay = 6 THEN 1 ELSE CanWork6 END,
    CanWork7 = CASE WHEN cd.WeekDay = 7 THEN 1 ELSE CanWork7 END
FROM
    #PS_EMPLOYEES AS Emps
    CROSS JOIN _PS_checkdays1 AS cd

在此处输入图像描述

标签： sqlsql-server

解决方案

加入前聚合：

UPDATE Emps
    SET CanWork1 = COALESCE(cd.CanWork1, Emps.CanWork1),
        CanWork2 = COALESCE(cd.CanWork2, Emps.CanWork2),
        . . .
    FROM #PS_EMPLOYEES Emps CROSS JOIN
         (SELECT MAX(CASE WHEN cd.WeekDay = 1 THEN 1 END) as CanWork1,
                 MAX(CASE WHEN cd.WeekDay = 2 THEN 1 END) as CanWork2,
                 . . . 
          FROM _PS_checkdays1 cd
         )  cd;

该UPDATE语句只更新每个匹配的行一次。如果JOIN生成多行，则选择任意行进行更新。

推荐阅读