How to Update a Row with an existing Row?

Question

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

[What have **you** tried??](http://www.whathaveyoutried.com) — marc_s, Apr 04 '13 at 07:57
Duplicate - http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server — Deepak Bala, Apr 04 '13 at 07:58
please post the complete error. it might contain hints about what the problem was with the query — scones, Apr 04 '13 at 08:15
The [`MERGE` statement](http://technet.microsoft.com/en-us/library/bb510625.aspx) in SQL Server 2008 and later is great for bulk updates. — Petrus Theron, Apr 04 '13 at 08:19
@pete can you give an example how to do the above with Merge statement — Imran Qadir Baksh - Baloch, Apr 04 '13 at 08:30

score 1 · Accepted Answer · answered Apr 04 '13 at 08:16

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

How to Update a Row with an existing Row?

1 Answers1