SQL UPDATE based on COUNT

Question

I have a table that looks as follows

ID   |   action   |  flag
1    |   A        |  1
1    |   A        |  1
1    |   B        |  1
2    |   A        |  1
2    |   A        |  1
2    |   B        |  1
2    |   B        |  1

I want to do the following: If for the same ID the value B in the action column appears more than 1 time, then I want to set the flag column for this ID to 0.

The result should look like this:

ID   |   action   |  flag
1    |   A        |  1
1    |   A        |  1
1    |   B        |  1
2    |   A        |  0
2    |   A        |  0
2    |   B        |  0
2    |   B        |  0

I know two ways to do this:

• Use a subquery: However, I don't want to use a subquery, because I deal with large tables, and a subquery deteriorates performance
• Use a temporary lookup table: I create a temporary lookup table, in which I store the IDs which have value B in the action colum more than 1 time, and I then join the temporary table with the original table to find the IDs for which I will set flag to 0

Is there another option besides the two explained above? Ideally in one query (without subquery and without temporary lookup table). I was thinking about something like a JOIN where the JOIN clause contains something like a GROUP BY and HAVING, but I wasn't successful until now..

Answer 1

Something like this should work for you:

UPDATE t
SET flag = 0
FROM Table t
INNER JOIN 
(
    SELECT Id
    FROM Table
    WHERE action = 'B'
    GROUP BY Id
    HAVING COUNT(*) > 1
) d ON t.Id = d.Id