Update SQL with Aliased tables still returns "table is ambiguous" error

Question

I am trying to run the below update but running into the "table is ambiguous" error.

UPDATE dbo.cg
SET cg.column = gId.ID
FROM    dbo.a
        INNER JOIN dbo.cg as cId ON cId.[a] = dbo.a.[c]
        INNER JOIN dbo.cg as gId ON gId.[a] = dbo.a.[b];

The table dbo.a contains data to update a value in cg based on a relationship to same table against a value in a different column. It is a self-referencing hierarchy.

As you can see, everything is aliased so I am a bit confused why this won't run.

Many thanks in advance for any help that can be provided.

Answer 1

In SQL Server, you should use the alias in the update, not the table. In addition, you have no alias called cg. So something like this:

UPDATE cId
SET column = gId.ID
FROM dbo.a a INNER JOIN
     dbo.cg cId
     ON cId.[a] = a.[c] INNER JOIN
     dbo.cg gId
     ON gId.[a] = a.[b];

Answer 2

Not to worry, solved it by luck.

I inner joined the table to itself in desperation ...

UPDATE dbo.cg
SET cg.column = gId.ID
FROM    dbo.a
        INNER JOIN dbo.cg as cId ON cId.[a] = dbo.a.[c]
        INNER JOIN dbo.cg as gId ON gId.[a] = dbo.a.[b]
        INNER JOIN cg ON cId.[a] = cg.[a];

If anyone could explain why that has worked, I would really appreciate understanding the MS SQL logic underneath.