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

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

Queues:

QueuesMeta:

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):

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.

>Solution :

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