Updating field value based on data across three separate tables

I have been trying to UPDATE the values of a field (IsException) from 0 to 1 in a table (g) based on corresponding data from a separate table (a) in two fields (name, date) when the combination of both appear in that separate smaller table. The problem I am running up against is that the ‘name’ field comes from two separate systems and does not match, however I have a third “translation” table (t) which should allow a 1:many relationship to help qualify the name discrepancy. So basically I want to use the table a data to drive the updates in the g table after adapting the name, so I think I would need a LEFT JOIN, but have not been able to get this to function properly.

Last attempt looks something like:

UPDATE g
SET [IsException] = 1 WHERE EXISTS
(SELECT 1 FROM a
WHERE g.Date = a.Date
AND (LEFT JOIN t ON a.Name = t.Aname) t.Gname = g.Name)

Sample data from the tables looks a bit like:

a
Name       Date
Tom        2021-03-01
Sue        2021-02-28

g
Name       Date           IsException
TomT       2021-02-28     0
TomT       2021-03-01     0
SueS       2021-02-28     0
SueS       2021-03-01     0
BobB       2021-02-28     0
BobB       2021-03-01     0

t
AName      GName
Tom        TomT
Sue        SueS
Bob        BobB

So the aim would be to get the second and third rows in table g to have the IsException flag field update from 0 to 1.

I’ve been staring at UPDATE and JOIN syntax for hours and poring over other similar queries, but haven’t been able to get this to function as intended. Any point in the right direction would be greatly appreciated.

Answer

Isn’t it just a straightforward case of 2 inner joins?

UPDATE G SET
    IsException = 1
FROM G
INNER JOIN T on T.GName = G.[Name]
INNER JOIN A on A.[Name] = T.AName
WHERE A.[Date] = G.[Date];

Advertisement

Answer