sql - 根据同一列的值更新列中的值

Question

我需要为以下内容编写一个 UPDATE SQL 查询（使用 SQL Server 2012）：

• 如果值 COLUMN = AC5 AND InputParameterName = CD
  然后更新值 COLUMN = R1_123 OF ROW WHERE InputParameterName = R AND 与上述条件相同的“bondcurescheduleid”

• 如果值 COLUMN = AC6 AND InputParameterName = CD
  然后更新值 COLUMN = R2_546 OF ROW WHERE InputParameterName = R 对于上述条件中的相同bondcurescheduleid

预期输出：

标签： sqlsql-serversql-server-2012

Answer 1

您可以尝试使用CASE表达式和现有逻辑进行更新以定位正确的记录组：

UPDATE t1
SET
    t1.Value = CASE t2.Value WHEN 'AC5' THEN 'R1_123'
                             WHEN 'AC6' THEN 'R1_546' END
FROM yourTable t1
INNER JOIN yourTable t2
    ON t2.BondCureScheduleId = t1.BondCureScheduleId
WHERE
    t2.Value IN ('AC5', 'AC6') AND
    t1.InputParameterName = 'R' AND
    t2.InputParameterName = 'CD';