PostgreSQL UPDATE statement with CASE and multiple conditions-CodePudding

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?

CodePudding user response：

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);