Search code examples
sqlmysql

How to Use IS NULL in SQL CASE statement

I want to write this but it doesn't work. In my case I need IS NULL

UPDATE CTRY1 ctrySts
SET startDate =

 (SELECT
    MIN(CTRY.VALIDATIONDate) 
FROM
    COUNTRY CTRY
JOIN
    LANGUAGE_DETAIL LNGE
        ON CTRY.DETAIL_FK = LNGE.DETAIL_PK 
WHERE
    CTRY.CODE = 'WDIZ' 
    AND CTRY.INDICATORCODE = (CASE
                                     WHEN CTRY1.INDICATORCODE IS NOT NULL THEN CTRY1.INDICATORCODE
                                     **ELSE IS NULL** 
                                     END)
                                     )
Solution

  • If I correctly understand your trouble, then you need that:

    AND (CTRY.INDICATORCODE = CTRY1.INDICATORCODE
     OR (CTRY.INDICATORCODE IS NULL AND CTRY1.INDICATORCODE IS NULL)
    )

    that is, either both have equal values or both are null