I have the query below that when run it says that 325 rows were affected. However, if I only run the SELECT portion of the query, I get 331 results returned. This means that when the whole query runs, only 325 rows are getting updated and not 331. I'm not sure what the discrepancy could be. I'd like it to update all the rows that it finds in the SELECT portion of the query
This update results in 325 rows updated
UPDATE A
SET
A.status = X.c
FROM
tableA A INNER JOIN (
SELECT tableB.c_id, COUNT(*) as c
FROM tableB
LEFT JOIN tableC
ON tableB.c_id = tableC.c_id
WHERE tableC.c_id != tableC.c_id_update
GROUP BY tableB.c_id) X
ON A.c_id = X.c_id
And running only the SELECT portion of the query returns 331 rows
SELECT tableB.c_id, COUNT(*) as c
FROM tableB
LEFT JOIN tableC
ON tableB.c_id = tableC.c_id
WHERE tableC.c_id != tableC.c_id_update
GROUP BY tableB.c_id
I'm really stuck on figuring out why there's 6 (331-325) records that don't get updated?! Thanks for your help.
Its probably that your addition join to TableA is removing 6 records. Look for it like this:
select x.* from
(
SELECT tableB.c_id, COUNT(*) as c
FROM tableB
LEFT JOIN tableC
ON tableB.c_id = tableC.c_id
WHERE tableC.c_id != tableC.c_id_update
GROUP BY tableB.c_id
) X
left join tableA A on A.c_id = X.c_id
where A.C_id is null
Essentially this is a left join, and you only return the rows where the left join is absent. It should tell you which 6 rows are missing from TableA
Evidently your inner select statement is returning 331 rows, of which 325 were successfully joined to tableA via ON A.c_id = X.c_id. So presumably 6 of the rows returned from your inner select do not match up with rows in tableA and so are dropped (since it's not an OUTER JOIN).
Now try:
select count(*)
FROM
tableA A INNER JOIN (
SELECT tableB.c_id, COUNT(*) as c
FROM tableB
LEFT JOIN tableC
ON tableB.c_id = tableC.c_id
WHERE tableC.c_id != tableC.c_id_update
GROUP BY tableB.c_id) X
ON A.c_id = X.c_id
The difference is the inner join