将数据复制到SQL Server中的同一表中的现有行

In SQL Server 2008, I want to update some of the rows with data from another row. For example, given the sample data below:

在SQL Server 2008中,我想用另一行的数据更新一些行。例如,给出以下示例数据:

ID   |     NAME           |    PRICE
---------------------------------------
 1   | Yellow Widget      |  2.99
 2   | Red Widget         |  4.99
 3   | Green Widget       |  4.99
 4   | Blue Widget        |  6.99
 5   | Purple Widget      |  1.99
 6   | Orange Widget      |  5.99

I want to update rows with ID 2, 3, and 5 to have the price of row 4.

我想更新ID为2,3和5的行,以获得第4行的价格。

I found a nice solution to update a single row at Update the same table in SQL Server that basically looks like:

我找到了一个很好的解决方案,在更新SQL Server中的同一个表时更新单行,基本上看起来像:

DECLARE  @src int = 4
        ,@dst int = 2  -- but what about 3 and 5 ?

UPDATE  DST
SET     DST.price = SRC.price
FROM    widgets DST
    JOIN widgets SRC ON SRC.ID = @src AND DST.ID = @dst;

But since I'm need to update multiple rows I'm not sure how the JOIN should look like. SRC.ID = @src AND DST.ID IN (2, 3, 5) ? (not sure if that's even valid SQL?)

但由于我需要更新多行,我不确定JOIN应该是什么样子。 SRC.ID = @src AND DST.ID IN(2,3,5)? (不确定这是否是有效的SQL?)

Also, if anyone can explain how the solution above does not update all the rows in the table since there is no WHERE clause, that would be great!

此外,如果任何人都可以解释上面的解决方案如何不更新表中的所有行,因为没有WHERE子句,那就太棒了!

Any thoughts? TIA!

有什么想法吗? TIA!

1 个解决方案

#1

You can use table variables to store the IDs to be updated:

您可以使用表变量来存储要更新的ID:

DECLARE @tbl TABLE(ID INT PRIMARY KEY);
INSERT INTO @tbl VALUES (2), (3), (5);

DECLARE @destID INT = 4

UPDATE widgets 
    SET price = (SELECT price FROM widgets WHERE ID = @destID)
WHERE
    ID IN(SELECT ID FROM @tbl)

Alternatively, you can store the source ID and destination ID in a single table variable. For this case, you need to store (2, 4), (3, 4) and (5, 4).

或者,您可以将源ID和目标ID存储在单个表变量中。对于这种情况,您需要存储(2,4),(3,4)和(5,4)。

DECLARE @tbl TABLE(srcID INT, destID INT, PRIMARY KEY(srcID, destID));
INSERT INTO @tbl VALUES (2, 4), (3, 4), (5, 4);

UPDATE s
    SET s.Price = d.Price
FROM widgets s
INNER JOIN @tbl t ON t.srcID = s.ID
INNER JOIN widgets d
    ON d.ID = t.destID

#1