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..

Reputation: 5686

SQL UPDATE based on COUNT

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..

Upvotes: 3

Answers (2)

Tim Biegeleisen

Reputation: 521259

Doing an update join of your original table to a subquery which uses aggregation to identify candidate ID values is the fastest way which comes to mind:

UPDATE t1
SET flag = 0
FROM yourTable t1
INNER JOIN
(
    SELECT ID
    FROM yourTable
    GROUP BY ID
    HAVING SUM(CASE WHEN action = 'B' THEN 1 ELSE 0 END) > 1
) t2
    ON t1.ID = t2.ID

Note that this subquery is not correlated, meaning SQL Server will only execute it once for the entire UPDATE. Therefore, it is not as big a penalty as you seemed to have imagined.

If you created a formal lookup table, you could in theory add indices to the join columns, and that might make the UPDATE faster. But there is a hassle with creating and maintaining a lookup table. In production, I would much rather have a single query used for updating.

Upvotes: 3

Zohar Peled

Reputation: 82474

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

Upvotes: 5

SQL UPDATE based on COUNT

Answers (2)

Related Questions