PostgreSQL UPDATE statement with CASE and multiple conditions

Question

I am trying to update a single column in a table based on 3 conditions. I tried to use a CASE statement and I can get 2 of the conditions to work but not the 3rd. Here's the criteria for the conditions:

If the field transpond = 0 then the field transpondertype should be 'N'
If the field transpond = 1 then the field transpondertype should be 'A'
If the field modesquip = 1 then the field transpondertype should be 'S'

And here's the SQL I came up with:

UPDATE "myDatabase".myTable
SET transpondertype=(CASE 
                       WHEN transpond=0 THEN 'N'      
                       WHEN transpond=1 THEN 'A'      
                       WHEN transpond=1 AND modesequip=1 THEN 'S'
                     END);

The transpondertype field gets updated properly with the first two conditions, it's the last WHEN statement that never updates correctly (it doesn't change the data at all). I tried WHEN modesequip=1 THEN 'S' originally but that didn't work either.

How can I get the statement to update the transpondertype field for all 3 parts of the CASE statement?

Himanshu · Accepted Answer · 2021-12-15 23:22:47Z

5

Try this

UPDATE "myDatabase".myTable
SET transpondertype=(CASE 
                       WHEN transpond=0 THEN 'N'      
                       WHEN transpond=1 THEN
                                      CASE  WHEN modesequip=1 
                                      THEN 'S'
                                      ELSE 'A'
                                      END
                       
                          END);

answered Dec 15, 2021 at 23:22

Himanshu

3,9802 gold badges16 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kalina Mielke · Accepted Answer · 2021-12-16 09:24:01Z

0

when transpond=1 and modesequip!=1 then 'A'

answered Dec 16, 2021 at 9:24

Kalina Mielke

1115 bronze badges

Collectives™ on Stack Overflow

PostgreSQL UPDATE statement with CASE and multiple conditions

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related