count（）可以用在case语句中吗？

Question

我已将csv文件中的数据复制到表stage_table中的列：

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

当同一个表中的aid存在多次时，我试图将表中的action_indicator列设为“Y”（默认情况下，该列设置为“N”）。你知道吗

我尝试了这个查询，但不起作用

update_address_query = ("update public.address set active_ind=case    
                         when count(aid)>1 then 'Y' end from address")    
cur.execute(update_address_query)

我收到的错误是：

psycopg2.errors.DuplicateAlias: table name "address" specified more than once

这是否可以通过其他方式加以纠正？你知道吗

此地址表中的列是

addrid

addr1

addr2

city

pstate

country

postalcd

contactnumber

active_indicator

示例输出应如下所示：

99801,No-13 4rthcrossst,GandiNagar,Bangalore,Karnataka,India,456009,7800912345,N

99802,No-14 5thcrossst,NehruNagar,Hyderabad,Telangana,India,556001,6800612345,N

99803,No-15 6thcrossst,SardarNagar,Ahmedabad,Gujarat,India,356009,9800912345,N

99804,No-16 7thcrossst,PatelNagar,Bangalore,Karnataka,India,456009,5800912345,N

99805,No-17 8thcrossst,AnnaNagar,Chennai,TamilNadu,India,456009,4800912345,N

99801,No-13 4rthcrossst,GandiNagar,Bangalore,Karnataka,India,456009,7800912345,Y

99802,No-14 5thcrossst,NehruNagar,Hyderabad,Telangana,India,556001,6800612345,Y

99803,No-15 6thcrossst,SardarNagar,Ahmedabad,Gujarat,India,356009,9800912345,Y

99804,No-16 7thcrossst,PatelNagar,Bangalore,Karnataka,India,456009,5800912345,Y

99805,No-17 8thcrossst,AnnaNagar,Chennai,TamilNadu,India,456009,4800912345,Y

99801,No-13 4rthcrossst,GandiNagar,Bangalore,Karnataka,India,456009,7800912345,Y

99802,No-14 5thcrossst,NehruNagar,Hyderabad,Telangana,India,556001,6800612345,Y

99803,No-15 6thcrossst,SardarNagar,Ahmedabad,Gujarat,India,356009,9800912345,Y

99804,No-16 7thcrossst,PatelNagar,Bangalore,Karnataka,India,456009,5800912345,Y

99805,No-17 8thcrossst,AnnaNagar,Chennai,TamilNadu,India,456009,4800912345,Y

Answer 1

考虑使用row_number()的window function：

with sub as (
   select *, 
          row_number() over(partition by aid order by pk_id) AS rn   - USE PRIMARY KEY
   from public.address
)

update public.address a
set active_ind = 'Y'
from sub
where a.pk_id = sub.pk_id         - USE UNIQUE IDENTIFIER OR PRIMARY KEY
  and sub.rn > 1

Online Demo

Answer 2

你的问题与你想要的结果不符。所有记录都会更新，因为所有记录在aid上都有重复项。 所以这种情况看起来像是数据库的设计问题。如果您的Postgres版本小于12（当前版本），您可以尝试使用OIDS列来查找最早的行，并用'N'标记为active_indicator。其余部分将标记为“Y”。 代码如下所示：

UPDATE test_so SET active_indicator = 'Y';

UPDATE test_so SET active_indicator = 'N'
FROM (SELECT *, oid, row_number() over (partition by addrid order by oid) AS rank
        FROM test_so) a
WHERE a.rank = 1
AND test_so.oid = a.oid;

但是使用时间戳来实现这个目的要好得多。你知道吗

Answer 3

如果我做对了，你想把active_ind设为'Y'当且仅当同一行中的aid在表中存在多次。可以使用相关子查询来获取count()。你知道吗

UPDATE public.address a1
       SET active_ind = CASE
                          WHEN (SELECT count(*)
                                       FROM public.address a2
                                       WHERE a2.aid = a1.aid) > 1 THEN
                            'Y'
                        END;

顺便说一下：这是一个CASE表达式，而不是一个语句。你知道吗

---

编辑：

如果不想触及其他行的active_ind，也可以在WHERE子句中添加这样的子查询。你知道吗

UPDATE public.address a1
       SET active_ind = 'Y'
       WHERE (SELECT count(*)
                     FROM public.address a2
                     WHERE a2.aid = a1.aid) > 1;