更新列如果 count(id) 在表中有多个 [英] Update Column If count(id) have more than one in table

问题描述

我有一张像下面这样的表格..

I have a table like by following..

ID  CustId  CustName Status
1     a1       A     NULL
2     a1       A     NULL
3     a2       B     NULL
4     a3       B     NULL
5     a4       C     NULL
6     a4       C     NULL
7     a5       D     NULL
8     a6       E     NULL

我想在 count(custid) > 时更新 status = 21 和 status = 1 当 count(custid) = 1我希望通过以下方式输出

I want to update the status = 2 when count(custid) > 1 and status = 1 when count(custid) = 1 and I want output like by following

ID  CustId  CustName Status
1     a1       A     1
2     a1       A     2
3     a2       B     1
4     a3       B     1
5     a4       C     1
6     a4       C     2
7     a4       D     2
8     a6       E     1

推荐答案

WITH CTE
(
select custid,count(1) cnt
from my_table
group by custid
)
UPDATE a SET a.status= CASE WHEN b.cnt = 1 THEN 1 ELSE 2 END
FROM my_table a
INNER JOIN CTE b
on a.custid=b.custid

这篇关于更新列如果 count(id) 在表中有多个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！