Alter Update语句

Question

如何修改这条update语句，以便在GENDERSPELLING表中找不到该值时，将GENDER设置为'U'？

UPDATE DWCUST DW
SET GENDER = (SELECT NEW_VALUE FROM GENDERSPELLING GS
              WHERE DW.GENDER = GS.INVALID_VALUE)
WHERE GENDER NOT IN ('M', 'F');

Answer 1

我想这应该行得通。

UPDATE DWCUST DW
SET GENDER = (
    SELECT CASE WHEN MAX(NEW_VALUE) IS NULL 
           THEN 'U'
           ELSE MAX(NEW_VALUE)
           END
    FROM GENDERSPELLING GS
    WHERE DW.GENDER = GS.INVALID_VALUE
)
WHERE GENDER NOT IN ('M', 'F');

Answer 2

您可以使用COALESCE (或Oracle的NVL)将NULL转换为'U'

update dwcust dw
set gender = 
  nvl((select new_value from genderspelling gs where dw.gender = gs.invalid_value), 'U')
where gender not in ('M', 'F');