SQL Server: Updating a column

Question

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 !

Bhupinder Singh · Accepted Answer · 2011-10-04 02:42:26Z

4

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');

edited Oct 4, 2011 at 2:42

Bhupinder Singh

1,0711 gold badge11 silver badges21 bronze badges

answered Sep 13, 2011 at 1:00

alistair

1,0749 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

alistair Over a year ago

If there are only a small number of additional codes like v it is acceptable to add additional intersects in the subquery one after the other.

alistair Over a year ago

I may have edited over someone's comment...apologies for that...still an overflow noob.

Lieven Keersmaekers Over a year ago

+1 nice and simple. I would have somehow aggregated the whole thing and made it more complex than it needed to be.

alistair Over a year ago

Thanks Lieven, An aggregated subquery was the first thing to pop into my head as well.

Collectives™ on Stack Overflow

SQL Server: Updating a column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related