Postgres UPDATE with CASE and RETURNING

Viewed 42

I am trying to update columns using CASE statement

access_status here is an ENUM type, so, I have to CAST it otherwise Postgres gives error of converting text to ENUM with CASE statement (without CASE statement, I don't get this error)

UPDATE table_1
SET
    access_status = 
        CASE
            WHEN access_status='ACTIVE' THEN 'REVOKED'
            ELSE access_status -- need to write this otherwise the value is set to NULL
        END::access_status,
    updated_at = 
        CASE
            WHEN access_status='ACTIVE' THEN NOW()
            ELSE updated_at
        END,
    updated_by = 
        CASE
            WHEN access_status='ACTIVE' THEN 1
            ELSE updated_by
        END
WHERE
    id = 4
RETURNING
    id

From above, the condition is on single column only. I need to update multiple columns when access_status='ACTIVE' else DO NOTHING.

Also, I am RETURNING id to know if the update has been performed or not (to return response accordingly from the API). Want to return id only if the update has been performed

Is it possible to do it something like this =>

UPDATE table_1
CASE
    WHEN access_status = 'ACTIVE' THEN SET access_status = 'REVOKED',
                                            updated_by = 1,
                                            updated_at = NOW()
END
WHERE
    id = 4
RETURNING
    id

So that, I don't need to write multiple CASE statements for each column update

0 Answers
Related