patternModerate
PostgreSQL; Update different column on condition using CASE statement
Viewed 0 times
postgresqlcaseupdateconditioncolumnstatementdifferentusing
Problem
I have a table which has different types of balance and I need to deduct the balance from different columns based on availability.
Here is the query (Sample) I tried to use but it didn't work
Thanks!
Here is the query (Sample) I tried to use but it didn't work
update table_A
set case
when column_A>table_B.balance then column_A
when column_B>table_B.balance then column_B
when column_C>table_B.balance then column_C
end =value
from table_B
on table_A.Id=table_B.idThanks!
Solution
It is to be something like (logic only)
update table_A
set column_A = case when (column_A>table_B.balance) then value else column_A end,
column_B = case when not (column_A>table_B.balance)
and (column_B>table_B.balance) then value else column_B end,
column_C = case when not (column_A>table_B.balance)
and not (column_B>table_B.balance)
and (column_C>table_B.balance) then value else column_C end
from table_B
on table_A.Id=table_B.idCode Snippets
update table_A
set column_A = case when (column_A>table_B.balance) then value else column_A end,
column_B = case when not (column_A>table_B.balance)
and (column_B>table_B.balance) then value else column_B end,
column_C = case when not (column_A>table_B.balance)
and not (column_B>table_B.balance)
and (column_C>table_B.balance) then value else column_C end
from table_B
on table_A.Id=table_B.idContext
StackExchange Database Administrators Q#212927, answer score: 10
Revisions (0)
No revisions yet.