Mar 24, 2014

The table '' is ambiguous. Sql server


Suppose we have created tblTarget using following sql script in sql server:

CREATE TABLE tblTarget(
    ID int IDENTITY(1,1) PRIMARY KEY,
    vcData varchar(500) NULL,
)

Now if will execute a sql query like this:

UPDATE tblTarget
    SET vcData = s.vcData
FROM  tblTarget t INNER JOIN tblTarget s
ON t.ID = s.ID

We will get error message:

The table 'tblTarget' is ambiguous.

Cause: We cannot use table which is in update clause more than one times in from clause.

Solution: Use table alias in update clause. For example:

UPDATE t
    SET vcData = s.vcData
FROM  tblTarget t INNER JOIN tblTarget s
ON t.ID = s.ID

No comments:

Post a Comment