用于更新列中具有相同值的行的T-SQL

I have a table lets say called FavoriteFruits that has NAME, FRUIT, and GUID for columns. The table is already populated with names and fruits. So lets say:

我有一个表叫做FavoriteFruits，它有列的NAME，FRUIT和GUID。该表已经填充了名称和水果。所以我们说：

NAME      FRUIT       GUID
John      Apple       NULL
John      Orange      NULL
John      Grapes      NULL
Peter     Canteloupe  NULL
Peter     Grapefruit  NULL

Ok, now I want to update the GUID column with a new GUID (using NEWID()), but I want to have the same GUID per distinct name. So I want all the John Smiths to have the same GUID, and I want both the Peters to have the same GUID, but that GUID different than the one used for the Johns. So now it would look something like this:

好的，现在我想用新的GUID更新GUID列（使用NEWID（）），但我希望每个不同的名称具有相同的GUID。所以我希望所有John Smiths拥有相同的GUID，我希望Peters拥有相同的GUID，但是GUID与用于Johns的GUID不同。所以现在它看起来像这样：

NAME      FRUIT       GUID
John      Apple       f6172268-78b7-4c2b-8cd7-7a5ca20f6a01
John      Orange      f6172268-78b7-4c2b-8cd7-7a5ca20f6a01
John      Grapes      f6172268-78b7-4c2b-8cd7-7a5ca20f6a01
Peter     Canteloupe  e3b1851c-1927-491a-803e-6b3bce9bf223
Peter     Grapefruit  e3b1851c-1927-491a-803e-6b3bce9bf223

Can I do that in an update statement without having to use a cursor? If so can you please give an example?

我可以在更新语句中执行此操作而无需使用游标吗？如果是这样，请举个例子？

Thanks guys...

多谢你们...

3 个解决方案

#1

Update a CTE won't work because it'll evaluate per row. A table variable would work:

更新CTE将无法正常工作，因为它将按行进行评估。表变量可以工作：

You should be able to use a table variable as a source from which to update the data. This is untested, but it'll look something like:

您应该能够使用表变量作为更新数据的源。这是未经测试的，但它看起来像：

DECLARE @n TABLE (Name varchar(10), Guid uniqueidentifier);

INSERT @n
SELECT Name, newid() AS Guid
FROM FavoriteFruits
GROUP BY Name;

UPDATE f
    SET f.Guid = n.Guid
FROM @n n
    JOIN FavoriteFruits f ON f.Name = n.Name

So that populates a variable with a GUID per name, then joins it back to the original table and updates accordingly.

因此，使用每个名称的GUID填充变量，然后将其连接回原始表并相应地更新。

#2

To clarify comments re a table expression in the USING clause of a MERGE statement.

澄清注释是MERGE语句的USING子句中的表表达式。

The following won't work because it'll evaluate per row:

以下内容无效，因为它将按行进行评估：

MERGE INTO FavoriteFruits
   USING (
          SELECT NAME, NEWID() AS GUID
            FROM FavoriteFruits
           GROUP 
              BY NAME
         ) AS source
      ON source.NAME = FavoriteFruits.NAME
WHEN MATCHED THEN
   UPDATE
      SET GUID = source.GUID;

But the following, using a table variable, will work:

但是，使用表变量的以下内容将起作用：

DECLARE @n TABLE 
(
 NAME VARCHAR(10) NOT NULL UNIQUE, 
 GUID UNIQUEIDENTIFIER NOT NULL UNIQUE
);

INSERT INTO @n (NAME, GUID)
   SELECT NAME, NEWID() 
     FROM FavoriteFruits
    GROUP 
       BY NAME;

MERGE INTO FavoriteFruits
   USING @n AS source
      ON source.NAME = FavoriteFruits.NAME
WHEN MATCHED THEN
   UPDATE
      SET GUID = source.GUID;

#3

There's a single-statement solution too, which, however, has some limitations. The idea is to use OPENQUERY(), like this:

还有一个单一语句解决方案，但是它有一些限制。想法是使用OPENQUERY（），如下所示：

UPDATE FavoriteFruits
SET GUID = n.GUID
FROM (
  SELECT NAME, GUID
  FROM OPENQUERY(
    linkedserver,
    'SELECT NAME, NEWID() AS GUID FROM database.schema.FavoriteFruits GROUP BY NAME'
  )
) n
WHERE FavoriteFruits.NAME = n.NAME

This solution implies that you need to create a self-pointing linked server. Another specificity is that you can't use this method on table variables nor local temporary tables (global ones would do as well as 'normal' tables).

此解决方案意味着您需要创建一个自指向链接的服务器。另一个特点是你不能在表变量上使用这个方法，也不能在本地临时表中使用这种方法（全局表和'普通'表一样）。

#1