oracle /SQL中的Update语句将值更新为null

Question

UPDATE FD DCS
  SET DCS.F_DISTANCE =
    (SELECT FD.F_DISTANCE
    FROM TMP FD
    WHERE FD.DATE_SID = DCS.DATE_SID
    AND FD.SID = DCS.SID
    AND FD.DA_SID = DCS.DASID
    AND FD.AA_SID = DCS.AA
    AND FD.F_DISTANCE IS NOT NULL
    )
WHERE DCS.BATCH_ID=BATCH_ID;

内部SQl查询正在将所有满意的值更新到FD表的列F_distance，但是子查询不匹配的地方是将FD表的F_Distance值更新为null。我不想将值更新为null。请建议一下该怎么做。

Answer 1

在ORACLE中，您可以使用NVL

UPDATE FD DCS
SET DCS.F_DISTANCE = NVL(
  (SELECT FD.F_DISTANCE
  FROM TMP FD
  WHERE FD.DATE_SID = DCS.DATE_SID
  AND FD.SID = DCS.SID
  AND FD.DA_SID = DCS.DASID
  AND FD.AA_SID = DCS.AA
  AND FD.F_DISTANCE IS NOT NULL
  ), DCS.F_DISTANCE)
WHERE DCS.BATCH_ID=BATCH_ID;

在这种情况下，如果结果为null，则NVL返回0(您的cna随您喜欢的值更改)。