Ambiguous column when update with remote server in SQL server?

Question

I have 2 SQL servers .

dstest\mssql2008 <--I'm currently at this instance ( server)
dstrn <-- another server

Both servers has the same table called :

EServices_Pages_Content

Goal : I need to update data on dstest from dstrn

On the current server (dstest) I have :

enter image description here

I can(!) access dstrn from dstest :

SELECT * FROM dstrn.weberp.dbo.EServices_Pages_Content WHERE pageid=80

enter image description here

OKay.

So where is the problem ?

As I told , I need to update data on dstest(current db) from a far server (dstrn) so I do :

UPDATE EServices_Pages_Content
SET    [Content]              =  a.Content
FROM   [dstrn].weberp.dbo.EServices_Pages_Content a
WHERE  PageID                 = a.pageID
       AND MasterEntityID     = a.masterEntityid
       AND LanguageID         = a.LanguageID
       AND PageID             = 80

But I get an error :

Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'PageID'.
Msg 209, Level 16, State 1, Line 5
Ambiguous column name 'MasterEntityID'.
Msg 209, Level 16, State 1, Line 6
Ambiguous column name 'LanguageID'.
Msg 209, Level 16, State 1, Line 7
Ambiguous column name 'PageID'.

I don't understand , I did use aliases , why does it tell me Ambiguous columns? How can I fix it ?

Edit : I found a way to make it work( see my answer) - but still can't understand why I need full table prefix in the where clause. - and can't use aliases to prevent Ambiguity

You need the table prefix in the where clause because there you can access and table. You could for instance compare two fields of the source table. Only "PageID" is ambiguous. While in the Set clause, you are only allowed to set fields of the table you are updating. — Stefan Steinegger, Jun 18 '15 at 06:57

Zohar Peled · Accepted Answer · 2015-06-18T09:11:12.173

3

Try this:

UPDATE Dest
SET    [Content] = a.Content
FROM   EServices_Pages_Content Dest 
INNER JOIN [dstrn].weberp.dbo.EServices_Pages_Content a
    ON Dest.PageID = a.pageID
    AND Dest.MasterEntityID = a.masterEntityid
    AND Dest.LanguageID = a.LanguageID
WHERE a.PageID = 80

Explanation:
Using an alias on just one of the tables is not enough.
Since both of the tables have the same names and the same column names, you need to specify an alias to the destination table as well, and use a multi-part name for it's columns.
Also, you are using an implicit join. Since explicit joins more readable, I would recommend to never use implicit joins.

edited Jun 18 '15 at 09:11

answered Jun 18 '15 at 06:34

Zohar Peled

79,642
10
69
121

no need for join here. it is already done in the where clause.I found the problem. still dont know why but 2 min... – Royi Namir Jun 18 '15 at 06:40
You might think you are not using a join, but what you are doing is an Implicit join. since explicit joins are a part of ANSI-SQL for more then 20 years now, I would recommend always using them over implicit joins. – Zohar Peled Jun 18 '15 at 06:44
I meant - there is no need to change the query structure. Ihad prefix problems. please see my answer., – Royi Namir Jun 18 '15 at 06:46
Your last line _Also, you where missing a join._ is incorrect.as we said -there is implicit join. so an explicit join is not mandatory. – Royi Namir Jun 18 '15 at 07:04
@RoyiNamir: yes, I've edited my answer to change that. – Zohar Peled Jun 18 '15 at 07:07
Your answer is non ansi either. the [**spec**](http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt) says : [this](http://i.imgur.com/Wrq8cWs.png). Also read [this](http://sqlblog.com/blogs/hugo_kornelis/archive/2008/03/10/lets-deprecate-update-from.aspx) ...."_All versions of the ANSI SQL standard that I checked agree that an UPDATE statement has three clauses – the UPDATE clause, naming the table to be updated; the SET clause...; and the optional WHERE clause to filter the rows to be updated. No FROM or JOIN – if you need data from a different table, use a subquery in the SET clause_" – Royi Namir Jun 18 '15 at 08:08
I think your advice to stick to explicit joins is good. But the implication of your explanation is that `UPDATE...FROM` is ANSI-SQL, which is incorrect. – Andriy M Jun 18 '15 at 08:12
I never said it was ansi-sql. it's tsql since we are talking about sql server. There is not much point of sticking with ansi sql alone where extended capabilities (like t-sql for sql server or pl/sql for oracle) can make your life easier... The only reference to ansi sql I've made was related to the join style. I could have just as easily write that using implicit joins in sql server is bad practice since t-sql support explicit joins. – Zohar Peled Jun 18 '15 at 08:12
2

All right, but there's no point in bringing the ANSI-SQL point if what you are proposing is not ANSI-SQL either. – Andriy M Jun 18 '15 at 08:14
an equivalent sentence would be 'since explicit joins are supported by sql server well beyond 15 years.`. the point is that implicit joins are deprecated... – Zohar Peled Jun 18 '15 at 08:16
@ZoharPeled You should clarify your answer. other readers might be under the influence(like me) that your code _is_ ansi compliant. – Royi Namir Jun 18 '15 at 08:20
@RoyiNamir: Done. I've removed all reference to ansi-sql and replaced it with a reference to the fact that implicit joins are deprecated since sql server 2005, including a link – Zohar Peled Jun 18 '15 at 08:24
2

Where exactly does it say that implicit joins are deprecated in SQL Server? Again, you've raised a valid point that explicit joins should be preferred over implicit ones, but please don't mislead readers with unfounded statements. It's the *proprietary outer join syntax* (`*=`, `=*`) that is indeed deprecated in SQL Server since 2005. It doesn't mean that the old inner join style is deprecated too. (You should pay more attention to the post you've linked in your answer, it actually says the same.) – Andriy M Jun 18 '15 at 08:24
@AndriyM: granted, implicit inner joins are still supported. How would you explain why it's better to use explicit joins over implicit joins? – Zohar Peled Jun 18 '15 at 08:28
Well, I could explain why *I* prefer them but it's your answer, not mine. Will you stop using explicit joins in UPDATE after learning that UPDATE FROM is not ANSI? If you are determined to continue using them, why? That should be in your answer. But since you ask, my reasons in favour of explicit joins are readability and maintainability. – Andriy M Jun 18 '15 at 08:37
As I've already wrote in a previous comment: "There is not much point of sticking with ansi sql alone where extended capabilities (like t-sql for sql server or pl/sql for oracle) can make your life easier...". I agree with your reasons for preferring explicit joins over implicit ones, but if I write my *personal* reasons, wouldn't it just be *opinion based*? – Zohar Peled Jun 18 '15 at 08:40
2

This is incorrect. Implicit join syntax (`FROM a, b, c`) is not being deprecated. It's still in 2003+ SQL standards and is not being deprecated by SQL-Server either. As AndriyM pointed, only proprietary outer join syntax (`*=`, `=*`) is under deprecation. – ypercubeᵀᴹ Jun 18 '15 at 09:07
Fine. I've edited my answer again. I hope now everybody is happy. – Zohar Peled Jun 18 '15 at 09:11

Royi Namir · Answer 2 · 2015-06-18T06:53:15.567

2

I have created 2 test tables ( on the same server) :

Table testA

enter image description here

Table testB

enter image description here

Now , for simplicity - let's say I want to update tableA from tableB but only only for pageId=2 :

So first I tried :

UPDATE testA a
SET    a.[Content] = b.Content
FROM   testB b
WHERE  a.PageID = b.pageID
       AND a.PageID = 2

But I got an error :

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'a'.

So then I tried :

UPDATE testA   
SET    [Content] = b.Content
FROM   testB b
WHERE  testA.PageID = b.pageID -- notice  prefix
       AND  testA.PageID = 2 -- notice  prefix

And Then :

(1 row(s) affected)

And now you can see it's ok :

enter image description here

edited Jun 18 '15 at 06:53

answered Jun 18 '15 at 06:45

Royi Namir

144,742
138
468
792

1

It's still an implicit join... I'm not a 100% sure, but I think that the reason you can use the alias from the update clause in your where clause is the same reason you can use an alias from a select clause in where clause. – Zohar Peled Jun 18 '15 at 06:54
@ZoharPeled Of course there is a join because of matching `Ints`. but my syntax is also valid for [update values from other tables](http://stackoverflow.com/questions/4920394/how-to-update-data-in-one-table-from-corresponding-data-in-another-table-in-sql) . I wanted to know why it doesnt work. – Royi Namir Jun 18 '15 at 06:55

score 1 · Answer 3 · answered Jun 18 '15 at 06:36

You need some join here:

UPDATE  d
SET     [Content] = a.Content
FROM    EServices_Pages_Content d
        JOIN [dstrn].weberp.dbo.EServices_Pages_Content s ON d.PageID = s.pageID
                                                             AND d.MasterEntityID = s.masterEntityid
                                                             AND d.LanguageID = s.LanguageID
                                                             AND d.PageID = 80

Ambiguous column when update with remote server in SQL server?

3 Answers3