6

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.

K7Buoy
  • 936
  • 3
  • 13
  • 22
  • `SET cg.column = ....`, well, you have 2 `cg` tables in your query, that's why you are getting that message – Lamak Dec 14 '15 at 15:56
  • Even though I have aliased the other two? I have looked about how would you suggest I alias the SET even further than I have done so. – K7Buoy Dec 14 '15 at 15:58

2 Answers2

17

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];
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Many thanks for the response Gordon. I have tried that I must say to get round this but I appreciate the quick response. – K7Buoy Dec 14 '15 at 16:00
4

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.

K7Buoy
  • 936
  • 3
  • 13
  • 22
  • 1
    I stumbled on the same issue today, joining on itself made the error go away and the update works. Really weird IMO – Jerry Aug 25 '21 at 11:03