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

Reputation: 33954

Update Row Id=1 FROM Row Id=2

I have a Product table include id and name. Let say I have,

Id Name
1  A
2  B
3  C

I want to update row where Id=2 with the value of row where Id = 1. This is what I am looking,

Id Name
1  A
2  A
3  C

I am trying different syntax but nothing working.

Update: The above example uses one column but I have many columns to update.

Upvotes: 0

Views: 214

Answers (2)

Fabio
Fabio

Reputation: 32445

You need update every column 

UPDATE Product
SET
    Product.Name = anotherRow.Name
    , Product.AnotherColumn = anotherRow.AnotherColumn,
    --, Other columns
FROM
    (
    SELECT
        Name
        , AnotherColumn
        --, other columns
    FROM Product
    WHERE ID = 1
    ) anotherRow
WHERE
    Product.ID = 2

Or if after data was copied, you will delete row where ID = 1, then update only ID column

Upvotes: 1

Antoan Milkov
Antoan Milkov

Reputation: 2228

You can do something like that:

UPDATE P1 SET P1.Name = P2.Name, P1.X = P2.X, ......
FROM Products P1 CROSS JOIN Products P2 WHERE P1.Id = 2 AND P2.Id = 1;

Upvotes: 2

Related Questions