T-SQL to update rows with the same value in a column

Question

I have a table called FavoriteFruits that has NAME , FRUIT and GUID for columns. The table already has names and fruits. So let's say:

 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 for a single name. So I want all John Smiths have the same GUID , and I want Peters have the same GUID , but this GUID different from the one used for Johns. Now it will look something like this:

 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 this in an update statement without having to use a cursor? If so, can you give an example?

Thanks guys...

Answer 1

Refresh CTE will not work, because it will be evaluated in a row . The table variable will work:

You should be able to use the table variable as the source from which to update the data. This has not been verified, but will look something like this:

 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 @nn JOIN FavoriteFruits f ON f.Name = n.Name 

So, to populate a variable with a GUID for the name, then it joins the source table and updates accordingly.

Answer 2

To clarify the re comments, the table expression in the USING clause of the MERGE operator.

The following actions will not work, because it will evaluate each 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; 

Answer 3

There is also a one-position solution, which, however, has some limitations. The idea is to use OPENQUERY() , for example:

 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 homing linked server. Another feature is that you cannot use this method for table variables or local temporary tables (global ones will do just like regular tables).