Query to join two tables and update one based on the counts from the other

Question

Let us say I have 2 tables Queues and QueuesMeta with the following sample data:

Queues:

QueueId	ItemName
1	Pencil
1	Eraser
2	Pencil
2	Eraser
2	Ruler

QueuesMeta:

QueuesMetaId	QueueId	Capacity	IsCountMatch
1	1	2	No
2	2	3	No
3	1	3	No
4	1	1	No

I need an update query that I plan to use in a stored procedure that would update the IsCountMatch column (pre-filled with "No") in the QueuesMeta table to either "Yes" or "No" based on whether the record count for the QueueId in the Queues table matches the stated capacity in the QueuesMeta table.

So, after the query is run, the QueuesMeta table should look like this:

QueuesMeta (updated):

QueuesMetaId	QueueId	Capacity	IsCountMatch
1	1	2	Yes
2	2	3	Yes
3	1	3	No
4	1	1	No

The QueueId in QueuesMeta table is not a unique column.

I have come up with a query that looks like this:

UPDATE QueuesMeta 
SET IsCountMatch = 'Yes'
WHERE QueueId IN (SELECT Q.QueueId
                  FROM QueuesMeta QM
                  INNER JOIN Queues Q ON QM.QueueId = Q.QueueId
                  WHERE QM.IsCountMatch = 'No'
                  GROUP BY QM.QueueId, QM.Capacity
                  HAVING QM.Capacity = COUNT(Q.QueueId))

But this does not seem to be working. A quick check reveals that because the QueueId is not unique in the QueuesMeta table, the COUNT(Q.QueueId) portion is returning a count of 6 (3 entries in QueuesMeta and 2 in Queues) instead of just 2.

Appreciate any help with the query.

Answer 1

It seems you have an extra join on QueuesMeta.

This type of thing is much easier to do with a correlated EXISTS. You simply want to know if the number of Queues rows that match by QueueId is the same as Capacity.

UPDATE QM
SET IsCountMatch = 'Yes'
FROM QueuesMeta QM
WHERE EXISTS (SELECT 1
    FROM Queues Q
    WHERE QM.QueueId = Q.QueueId
    -- implicitly group by empty set
    HAVING QM.Capacity = COUNT(*)
)
  AND QM.IsCountMatch = 'No';

You can also use a correlated comparison

UPDATE QM
SET IsCountMatch = 'Yes'
FROM QueuesMeta QM
WHERE QM.Capacity = (
    SELECT COUNT(*)
    FROM Queues Q
    WHERE QM.QueueId = Q.QueueId
    -- implicitly group by empty set
)
  AND QM.IsCountMatch = 'No';

db<>fiddle