CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
user3461502
user3461502

Reputation: 323

how to update column based on a several column of another table when certain condition is found?

I have the the following table that looks like this: enter image description here

Based on the first table, I need to update my second table that contains the date in columns. So from the three records above, I need to have only one record and put the date that matches with the corresponding STATUS.

 when status = 'CLAIMED' put DATETIME in CLAIMED_DATETIME
 when status = 'BOUGHT' put DATETIME in BOUGHT_DATETIME
 when status = 'RETURNED' put DATETIME in RETURNED_DATETIME

Currently, my table looks like this: enter image description here

I need the table to look like this: enter image description here

Do you know how I would do this?

Upvotes: 0

Views: 45

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

So one method to do selective assignments is to update all

UPDATE table SET
    claimed_datetime = IFF(status='CLAIMED', datetime, claimed_datetime),
    bought_datetime = IFF(status='BOUGHT', datetime, bought_datetime),
    returned_datetime = IFF(status='RETURNED', datetime, returned_datetime)
WHERE blar blar;

The other is to use a MERGE command, and put sub-constraints, that check you do three times, and thus only write the column for that state.

MERGE INTO TARGET
    USING src 
        ON target.number_id = src.number_id
    WHEN MATCHED AND status='CLAIMED' THEN 
        UPDATE SET 
            target.claimed_datetime = src.datetime
    WHEN MATCHED AND status='BOUGHT' THEN 
        UPDATE SET 
            target.bought_datetime = src.datetime
    WHEN MATCHED AND status='RETURNED' THEN 
        UPDATE SET 
            target.returned_datetime = src.datetime;

Upvotes: 1

Related Questions