SQL Query根据同一个表中其他列的值更新列

Ok this is difficult to phrase, so here goes...

好的,这很难说,所以这里......

I am using MS SQL Server 2008 R2. I have a temp table that lets say has two already populated columns. There is a third empty column I want to populate based on the value of the first two columns. What I want to do is create a guid (using NEWUID()) for each matching combo of col1 and col2. Here is a visual example:

我正在使用MS SQL Server 2008 R2。我有一个临时表,可以说有两个已经填充的列。我想根据前两列的值填充第三个空列。我想要做的是为col1和col2的每个匹配组合创建一个guid(使用NEWUID())。这是一个视觉示例:

Lets say I have a temp table that looks like this initially:

让我说我有一个最初看起来像这样的临时表:

Name    Activity    SpecialId
James   Running     
James   Running
James   Walking
John    Running
John    Running
John    Walking

I want it to get updated with new GUIDs so that it looks like this:

我想让它更新新的GUID,使它看起来像这样:

Name    Activity    SpecialId
James   Running     SOMEFAKEGUID_1
James   Running     SOMEFAKEGUID_1
James   Walking     SOMEFAKEGUID_2
John    Running     SOMEFAKEGUID_3
John    Running     SOMEFAKEGUID_3
John    Walking     SOMEFAKEGUID_4

Notice how a new GUID is created for each matching pair. So the James/Running combo has the same GUID for all James/Running combos... and the John/Running also has the same GUID for the John/Running combos, but not the same GUID as the James/Running combos do.

请注意如何为每个匹配对创建新的GUID。因此,James / Running组合对于所有James / Running组合具有相同的GUID ...而John / Running也具有与John / Running组合相同的GUID,但与James / Running组合的GUID不同。

I tried to make that as clear as possible, but hopefully that isn't clear as mud!

我试图尽可能清楚地表明这一点,但希望这不是很清楚!

Can someone show me what the SQL query would look like in order to update that temp table with the correct GUIDs?

有人可以告诉我,为了使用正确的GUID更新临时表,SQL查询会是什么样子?

Thanks in advance.

提前致谢。

Ryan

3 个解决方案

#1

Using NEWID() seems to be a pain. Using it a CTE creates a sperate ID, so you need some intermediary table instead.

使用NEWID()似乎很痛苦。使用它CTE创建一个sperate ID,所以你需要一些中间表。

Declare @Table as table (name varchar(20), activity varchar(20) , SpecialID uniqueidentifier)
Declare @DistinctTable as table (name varchar(20), activity varchar(20) , SpecialID uniqueidentifier)

INSERT INTO @Table 
(name, activity)
values
('James','Running'),    
('James','Running'),
('James','Walking'),
('John','Running'),
('John','Running'),
('John','Walking')



WITH distinctt 
     AS (SELECT DISTINCT name, 
                         activity 
         FROM   @Table) 
INSERT INTO @DistinctTable 
SELECT name, 
       activity, 
       Newid() 
FROM   distinctt 

UPDATE @Table 
SET    specialid = dt.specialid 
FROM   @Table t 
       INNER JOIN @DistinctTable dt 
         ON t.activity = dt.activity 
            AND t.name = dt.name 

SELECT * FROM @Table

Produces

name                 activity             SpecialID
-------------------- -------------------- ------------------------------------
James                Running              AAA22BC5-51FE-43B3-8CC9-4C4A5B4CC981
James                Running              AAA22BC5-51FE-43B3-8CC9-4C4A5B4CC981
James                Walking              1722B76B-5F17-4931-8D7C-2ECADB5A4DFD
John                 Running              FBC1F86B-592D-4D30-ACB3-80DA26B00900
John                 Running              FBC1F86B-592D-4D30-ACB3-80DA26B00900
John                 Walking              84282844-AAFD-45CA-9218-F7933E5102C6

#2

I'm sure there are better ways to do this, but you can try the following:

我确信有更好的方法可以做到这一点,但您可以尝试以下方法:

WITH TableId AS
(
    SELECT DISTINCT Name, Activity
    FROM YourTable
)

UPDATE A
SET A.SpecialId = B.SpecialId
FROM YourTable A
INNER JOIN (SELECT Name, Activity, NEWID() SpecialId FROM TableId) B
ON A.Name = B.Name AND A.Activity = B.Activity

#3

Well, I know that you're not using mySQL, but this is how it would work in mySQL (tested)

好吧,我知道你没有使用mySQL,但这就是它在mySQL中的运行方式(测试过)

update temp_table, (
select uuid() as spec_key, name, activity from (
select distinct name, activity from temp_table) as anotherTemp) as anotheranotherTemp
set specialID = anotheranotherTemp.spec_key
where temp_table.Activity = anotheranotherTemp.activity 
and temp_table.Name = anotheranotherTemp.name;

It LOOKS like this would work in SQL 2008 (not-tested)

它看起来像这样在SQL 2008中工作(未经测试)

MERGE INTO temp_table AS tt
  USING      (
    select newId() as spec_key, name, activity from (
    select distinct name, activity from temp_table) as anotherTemp 
      ON   anotherTemp.activity       = tt.activity
      and anotherTemp.name = tt.name
WHEN MATCHED
  THEN UPDATE
    SET        specialID = anotherTemp.spec_key;

Performance would not be good though.

虽然表现不佳。

#1