将数据复制到 SQL Server 中同一表中的现有行 [英] Copy Data To Existing Rows Within Same Table in SQL Server

问题描述

在 SQL Server 2008 中，我想用另一行的数据更新某些行.例如，给定以下示例数据:

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

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

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

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

我在 找到了一个很好的解决方案来更新单行更新 SQL Server 中的同一个表，基本上如下所示:

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

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;

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

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?)

另外，如果有人能解释上面的解决方案如何不更新表中的所有行，因为没有 WHERE 子句，那就太好了！

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!

有什么想法吗?TIA！

Any thoughts? TIA!

推荐答案

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

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

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)

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

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).

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

这篇关于将数据复制到 SQL Server 中同一表中的现有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！