我正在尝试使用条件子选择进行更新,该子选择可能返回null …
UPDATE
aTable SET
aColumn =
(
SELECT TOP 1
CASE
WHEN bTable.someColumn = 1 THEN someValue1
WHEN bTable.someColumn = 2 THEN someValue2
ELSE someValue3
END
FROM
bTable
WHERE
bTable = @someCriteria
ORDER BY
someSortColumn
) WHERE
aTable.id = @someId;
如果“bTable = @someCriteria”子句导致没有从SELECT返回结果,它会尝试将NULL插入“aColumn”,在本例中是一个NOT NULL列.
题
如何在这种情况下单独留下“aColumn”？
非常感谢.
解决方法
...
aColumn =
ISNULL(
(
SELECT TOP 1
CASE
WHEN bTable.someColumn = 1 THEN someValue1
WHEN bTable.someColumn = 2 THEN someValue2
ELSE someValue3
END
FROM
bTable
WHERE
bTable = @someCriteria
ORDER BY
someSortColumn
),aColumn)
...
