Update Set column only if its NULL else move to next column and so on update with same data-CodePudding

I want to update a column when it's only NULL, else update the same data in the next column and so on.

I don't want to write 4 IF conditions, can it be possible in single CASE like below?

Below is something I am trying to achieve.

UPDATE I 
        SET 
        (CASE 
            WHEN I."CA_Status1" ISNULL THEN I."CA_Status1"
            WHEN I."CA_Status1" IS NOTNULL THEN I."CA_Status2"
            WHEN I."CA_Status2" IS NOTNULL THEN I."CA_Status3"
            WHEN I."CA_Status3" IS NOTNULL THEN I."CA_Status4"
            END
            )
            = "7".StatusCode

        ,I."ENC" = "7".ActionCode

        FROM [dbo].[Out_P] I 
        INNER JOIN #TempOut_P "7" 
        ON I.ID = "7".Number

CodePudding user response：

Since the conditions that determine whether or not a particular column is updated are related, selecting the target column could be done in a CROSS APPLY. This would simplify the resulting assignments, making them consistent and easier to read.

UPDATE I
SET
    CA_Status1 = CASE WHEN S.Selector = 1 THEN "7".StatusCode ELSE I.CA_Status1 END,
    CA_Status2 = CASE WHEN S.Selector = 2 THEN "7".StatusCode ELSE I.CA_Status2 END,
    CA_Status3 = CASE WHEN S.Selector = 3 THEN "7".StatusCode ELSE I.CA_Status3 END,
    CA_Status4 = CASE WHEN S.Selector = 4 THEN "7".StatusCode ELSE I.CA_Status4 END,
    ENC = "7".ActionCode
FROM dbo.Out_P I 
INNER JOIN #TempOut_P "7" 
    ON I.ID = "7".Number 
CROSS APPLY (
    SELECT Selector = CASE
        WHEN I.CA_Status1 IS NULL THEN 1
        WHEN I.CA_Status2 IS NULL THEN 2
        WHEN I.CA_Status3 IS NULL THEN 3
        WHEN I.CA_Status4 IS NULL THEN 4
        END
) S