Reputation: 1071
I have a TblA
ID Match Code Status
1 001 A
2 001 B
3 002 A
4 003 A
5 003 V
6 004 A
7 004 B
I want to populate Status with 'FAIL' according to : Code "A" and "B" should both exist for every match number. For 001,002,003 both A, B should exist. if not, FAIL the whole Match. Expected table:
ID Match Code Status
1 001 A NULL
2 001 B NULL
3 002 A FAIL
4 003 A FAIL
5 003 V FAIL
6 004 A NULL
7 004 B NULL
Thanks !
Upvotes: 1
Views: 122
Reputation: 1074
Here you go:
update [TblA]
set [Status] = 'FAIL' where
Match NOT in
(select match from tblA where Code = 'A'
intersect
select match from tblA where Code = 'B');
Upvotes: 4