CODEWITHSUNDEEP
sqlsql-server-2008
Imran Qadir Baksh - Baloch
Imran Qadir Baksh - Baloch

Reputation: 33944

How to Update a Row with an existing Row?

I have a product table. I have some empty rows. I want to fill these rows with an existing row. For example, if I have 

Product
-----------------------------------
ID   Name  Desc  ---------------
1     A     A1   ---------------
2     B     B1   ---------------
3     C     C1   ---------------

I want to copy all the items of ID=1 into ID = 3 row.

Tried this but getting error, Ambigious

UPDATE [Products]
SET DP.[Name] = SP.Name 
    ,DP.[Desc] = SP.DESC                          
FROM    [Products] DP
       INNER JOIN [Products] SP ON SP.ID = 3
WHERE DP.ID = 1

Upvotes: 0

Views: 85

Answers (1)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

You need to use alias - either DP or SP in UPDATE and not Products itself because it's ambiguous between the two.

UPDATE DP
SET DP.[Name] = SP.Name 
    ,DP.[Desc] = SP.[DESC]                          
FROM    [Products] DP
INNER JOIN [Products] SP ON SP.ID = 3
WHERE DP.ID = 1

Upvotes: 1

Related Questions