如何在条件为真时使用CASE更新列

Question

我有一个带有列的表LOADING_ZONE

---

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

---

另一个表STAGE_TABLE有列

---

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

---

我已使用以下方法将加载\u区域的数据插入到阶段\u表中：

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

当两个表中都有相同的辅助时，我想把action_指示器设为'U'，其他的'I'

我尝试了这个，但收到一个错误：

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

错误是：

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

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

Answer 1

您可以使用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);

如果将所有指示符初始化为'I'（使用UPDATE或DEFAULT），则可以在插入数据时执行此操作：

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;

Answer 2

我已经创建了两个示例表（test和test1），分别对应于STAGE\u表和LOADING\u ZONE，以便在我的末尾复制用例。下面的更新查询将为您提供预期的输出。你知道吗

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

输出

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