我正在尝试使用条件子选择进行更新,该子选择可能返回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) ...