How to UPDATE a table on SQL Server with multiple Joins on the updated table? (FROM FROM JOIN)

sql, sql-server, sql-update

Solution

This works on Sql Fiddle.

UPDATE o
SET parent_id = o2.ID
    FROM recert O
    JOIN c C ON C.ID = O.country_id
    JOIN recert P ON O.parent_id = P.ID and p.country_id <> O.country_id
    JOIN recert o2 on o2.name = p.name and c.ID = o2.country_id
    JOIN c c2 on c2.ID = o2.country_id
WHERE O.ID = o2.ID

I suppose the problem arose because you tried to re-alias an alias, but I'm not sure.

Problem

How to UPDATE a table on SQL Server with multiple Joins on the updated table? In MySQL you can define a Alias for updated table, but how does it works with TSQL. ``` UPDATE recert.ou --#1-- In MSSQL/TSQL no alias allowed SET parent_id = o2.ID FROM recert.ou as O JOIN recert.country C ON C.ID = O.country_id JOIN recert.ou P ON O.parent_id = P.ID and p.country_id <> O.country_id JOIN recert.ou o2 on o2.name = p.name and c.ID = o2.country_id JOIN recert.country as c2 on c2.ID = o2.country_id WHERE O.ID = o2.ID ``` ``` RESULT: *The table 'o' is ambiguous.* ```

Original source