Geek Answers Handbook

SQL Query to update a column based on the values ​​of other columns in the same table

Well, it's hard to call a phrase, so here goes ...

I am using MS SQL Server 2008 R2. I have a temporary table that allows you to use two already filled columns. There is a third empty column that I want to fill based on the value of the first two columns. I want to create guid (using NEWUID ()) for each corresponding combination of col1 and col2. Here is a good example:

Suppose I have a temporary table that looks like this:

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

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

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

, GUID . , James/Running GUID James/Running... John/Running GUID John/Running, GUID, James/Running.

, , , , !

- , SQL, GUID?

.

+5
3

NEWID() . , CTE , -.

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 


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
+3

, , :

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
0

Well, I know that you are not using mySQL, but this is how it will work in mySQL (verified)

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;

SEE how it will work in SQL 2008 (not verified)

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.

0
source

More articles:


All Articles