CODEWITHSUNDEEP
sqlms-accessvbams-access-2010
LuckyLuke82
LuckyLuke82

Reputation: 604

Access SQL - One field doesn't exist, at least one of other do exist

I need to update Checkbox in table, If same ID in another table doesn't exist, but at least one of other fields do exist. Here is what I have:

UPDATE TableA
       INNER JOIN TableB_
       ON TableB_Name=TableA.Name OR TableB.Surname=TableA.Surname
       AND NOT TableB.ID=TableA.ID
       SET TableA.Checkbox=True

This SQL is placed in load event of form. What am I doing wrong ?

EDIT: What I want to do - 

TableA

ID    Name  Surname 
200   John  Mills

TableB

ID    Name  Surname
300   Jane  Mills

You notice that in both table there is a match in "Surname" field. I want Checkbox to be checked in this case, BUT ID MUST BE DIFFERENT.

In other words, I want to set Checkbox to True if there is a record with DIFFERENT ID, but one of the other fields are same.

Upvotes: 0

Views: 435

Answers (1)

sagi
sagi

Reputation: 40481

Use NOT EXISTS() , your query checks if there is a record with another ID, it doesn't check if ALL records have other ID :

UPDATE TableA
SET TableA.checkbox = true
WHERE NOT EXISTS(SELECT 1 FROM TableB
                 WHERE (TableB_Name=TableA.Name OR TableB.Surname=TableA.Surname)
                       AND TableB.ID=TableA.ID)

This will update the field to true only if a record with the same name/surname and ID doesn't exists.

If 1 record with different ID is enough, you can use EXISTS() on the same principal : 

UPDATE TableA
SET TableA.checkbox = true
WHERE EXISTS(SELECT 1 FROM TableB
             WHERE (TableB_Name=TableA.Name OR TableB.Surname=TableA.Surname)
                   AND TableB.ID<>TableA.ID)

Maybe you meant a combination , a record with the same ID doesn't exists, but exists one with another ID.. Then :

UPDATE TableA
SET TableA.checkbox = true
WHERE NOT EXISTS(SELECT 1 FROM TableB
                 WHERE (TableB_Name=TableA.Name OR TableB.Surname=TableA.Surname)
                       AND TableB.ID=TableA.ID)
 AND EXISTS(SELECT 1 FROM TableB
            WHERE (TableB_Name=TableA.Name OR TableB.Surname=TableA.Surname)
                  AND TableB.ID<>TableA.ID)

Upvotes: 2

Related Questions