Number of Rows is Different Between UPDATE and SELECT Statement in MS Access SQL

Question

I have exactly the same problem as in this thread Different number of rows affected when using select or update with the same parameters - PostgreSQL but I am using MS Access so the solution does not work for me. (Or I am doing it wrong)

These 2 statements return a different number of rows:

SELECT c.hole_id, c.level, c.section, c.valid, c.holetype, a.hole_id
FROM collar AS c LEFT JOIN assay AS a
ON a.hole_id = c.hole_id
WHERE c.holetype="Channel" AND a.hole_id Is Null;

And

UPDATE collar AS c 
SET c.valid = no
WHERE EXISTS
     (SELECT c.hole_id, c.level, c.section, c.valid, c.holetype, a.hole_id   
     FROM collar AS c LEFT JOIN assay AS a
     ON a.hole_id = c.hole_id
     WHERE c.holetype="Channel" AND a.hole_id Is Null);

The basic logic is that any record in collar that does not have a related record in assay (hole_id is the primary key/foreign key) must be no in the field valid.

So I saw the answer saying that UPDATE is self-joining, so I get what the solution is but I can't make this work in Access - I get no rows returned.

UPDATE collar AS c, assay AS a
SET c.valid = no
WHERE a.hole_id = c.hole_id AND c.holetype="Channel" AND a.hole_id Is Null;

Any suggestions?

Answer 1

You want to update:

any record in collar that does not have a related record in assay

which means that you should use NOT EXISTS:

UPDATE collar AS c 
SET c.valid = no
WHERE c.holetype = 'Channel' 
  AND NOT EXISTS (
        SELECT *
        FROM assay AS a
        WHERE a.hole_id = c.hole_id
      );

If you want to use a join in the UPDATE statement then the correct syntax for Access is:

UPDATE collar AS c  
LEFT JOIN assay AS a ON a.hole_id = c.hole_id
SET c.valid = no
WHERE c.holetype='Channel' AND a.hole_id IS NULL;