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