HiveBrain v1.2.0
Get Started
← Back to all entries
patternModerate

PostgreSQL; Update different column on condition using CASE statement

Submitted by: @import:stackexchange-dba··
0
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

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.id


Thanks!

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.id

Code 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.id

Context

StackExchange Database Administrators Q#212927, answer score: 10

Revisions (0)

No revisions yet.