How to update the column when the condition is true using CASE

Question

I have a table LOADING_ZONE with columns

aid, a_name, addrid, addr1, addr2, city, pstate, country, postalcd, contactnumber, attendancekey, 
attendancedate, attendedyesno

and another table STAGE_TABLE with columns

aid, a_name, addrid, addr1, addr2, city, pstate, country, postalcd, contactnumber, attendancekey, 
attendancedate, attendedyesno,action_indicator

I have inserted the data from loading_zone into stage_table using:

INSERT INTO stage_table(aid, a_name, addrid, addr1, addr2, city, pstate, country, postalcd, 
contactnumber, attendancekey, attendancedate, attendedyesno)
 

SELECT aid, a_name, addrid, addr1, addr2, city, pstate, country, postalcd, contactnumber, attendancekey, attendancedate, attendedyesno

FROM loading_zone

and I want to make action_indicator as 'U' when there exists same aid in both tables else 'I'

I tried this but receiving an error:

assign= "update stage_table set action_indicator = (CASE when loading_zone.aid=stage_table.aid then 'U' else 'I' end)"

The error is:

psycopg2.errors.UndefinedTable: missing FROM-clause entry for table "loading_zone"

LINE 1: ...ate stage_table set action_indicator = (CASE when loading_zone...

Check out the accepted answer here: https://stackoverflow.com/questions/7869592/how-to-do-an-update-join-in-postgresql — mechanical_meat, Dec 24 '19 at 06:12
When you `INSERT` into `stage_table`, there's no existing row, and therefore no existing aid to compare with. Therefore, since you are `INSERT`ing the `aid` from `loading_zone`, after the `INSERT` is done, `aid` will be the same for both tables, so you might as well hard-code `action_indicator='U'` -- if this understanding is incorrect, please clarify your question. Further, if you are looking to `UPDATE`, can you indicate their relationship (i.e., which col is primary key, and are there foreign keys involved?) — richyen, Dec 24 '19 at 06:18
yes, that is what I want to make but then if I create aid as primary key I am receiving duplicates error so in order to avoid it I made an extra column **stg** as primary key with datatype as serial — anonymus, Dec 24 '19 at 06:28
update stage_table set action_indicator = CASE WHEN loading_zone.aid=stage_table.aid THEN 'U' ELSE 'I' END from loading_zone try this. — Bharti Mohane, Dec 24 '19 at 06:45

score 0 · Answer 1 · answered Dec 24 '19 at 07:44

I have created two sample tables(test and test1) against STAGE_TABLE and LOADING_ZONE to replicate the use case at my end. Following update query will give you the expected output.

create table test (aid integer, action_indicator character varying(1));
create table test1 (aid integer);

insert into test(aid) values(1);
insert into test(aid) values(2);
insert into test(aid) values(3);
insert into test(aid) values(4);

insert into test1(aid) values(1);
insert into test1(aid) values(2);
insert into test1(aid) values(3);


update test set action_indicator=a.action_indicator from (select aid,case when test.aid in(select aid from test1) then 'U' else 'I' end as action_indicator 
from test)a where a.aid=test.aid

select * from test

Output

1   "U"
2   "U"
3   "U"
4   "I"

score 0 · Answer 2 · answered Dec 24 '19 at 12:10

You can use update:

update stage_table st
    set action_indicator =
        (case when exists (select 1
                           from loading_zone lz
                           where lz.aid = st.aid
                          )
              then 'U' else 'I'
         end);

If you initialize all the indicators to 'I' (using either UPDATE or DEFAULT), then you can do this when you insert the data:

INSERT INTO stage_table (aid, a_name
                         addrid, addr1, addr2, city, pstate, country, postalcd,
                         contactnumber, attendancekey, attendancedate, attendedyesno,
                         action_indicator
                        )
    SELECT aid, a_name,
           addrid, addr1, addr2, city, pstate, country, postalcd,
           contactnumber, attendancekey, attendancedate, attendedyesno,
           'U'
    FROM loading_zone;

How to update the column when the condition is true using CASE

2 Answers2