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

sql sql-update table-alias
K7Buoy picture K7Buoy · Dec 14, 2015 · Viewed 8k times · Source

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

Gordon Linoff picture Gordon Linoff · Dec 14, 2015

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];

Related questions

SQL update from one Table to another based on a ID match

I have a database with account numbers and card numbers. I match these to a file to update any card numbers to the account number, so that I am only working with account numbers. I created a view linking the …

Read More
How can I do an UPDATE statement with JOIN in SQL Server?

I need to update this table in SQL Server with data from its 'parent' table, see below: Table: sale id (int) udid (int) assid (int) Table: ud id (int) assid (int) sale.assid contains the correct value to update ud.…

Read More
Oracle SQL: Update a table with data from another table

Table 1: id name desc ----------------------- 1 a abc 2 b def 3 c adf Table 2: id name desc ----------------------- 1 x 123 2 y 345 In oracle SQL, how do I run an sql update query that can update Table 1 with Table 2's name and desc using …

Read More