开发者

SQL Server: FAILING extra records

开发者 https://www.devze.com 2023-04-07 14:20 出处:网络
I have a tableA (ID int, Match varchar, code char, status = char) IDMatch code Status 101 123A 102 123B 103 123C

I have a tableA (ID int, Match varchar, code char, status = char)

ID  Match code Status
101 123    A
102 123    B
103 123    C
104 234    A
105 234    B
106 234    C
107 234    B
108 456    A
109 456    B
110 456    C

I want to populate status with 'FAIL' when: For same match, there exists code different than (A,B or C) or the code exists multiple times. In other words, code can be only (A,B,C) and it should exists only one for same match, else fail. So, expected result would be:

ID  Match code Status
101 123    A   NULL
102 123    B   NULL
103 123    C   NULL
104 234    A   NULL
105 234    B   NULL
106 234    C   NULL
107 234    B   FAIL
108 456    A   NULL
109 456    B   NULL
110 456开发者_如何转开发    C   NULL

Thanks


No guarantees on efficiency here...

update tableA
set status = 'FAIL'
where ID not in (
     select min(ID) 
     from tableA
     group by Match, code)
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号