Table data update-CodePudding

Id type name deviceName
1
2 A2, 3, B
3 4 C
4 2 AA
5 3 FF
6 4 CC

How to type=3 name column values in the data update to type=2 deviceName,
Namely id=1 line deviceName=B
Id=4 rows deviceName=FF

CodePudding user response:

 

IF OBJECT_ID (N 'TEMPDB for. DBO. # T') IS NOT NULL 
DROP TABLE # T 
GO 

The CREATE TABLE # T 
(ID INT IDENTITY (1, 1), 
The TYPE INT, 
The NAME VARCHAR (10), 
DEVICENAME VARCHAR (10)) 

INSERT INTO # T (TYPE, NAME) 
SELECT 2, 'A' UNION ALL 
SELECT 3, 'B' UNION ALL 
SELECT 4, 'C' UNION ALL 
SELECT 2, 'AA' UNION ALL 
SELECT 3, 'FF' UNION ALL 
SELECT 4, 'CC' 

GO 

WITH CTE 
AS 
(SELECT *, ROW_NUMBER () OVER (PARTITION BY TYPE ORDER BY ID) AS SEQ FROM # T WHERE TYPE IN (2, 3)) 

UPDATE # T 
The SET DEVICENAME=D.N AME 
The FROM # T A 
The JOIN 
(SELECT C.I D, B.N AME 
The FROM CTE B 
JOIN the CTE C ON B.T YPE=3 AND C.T YPE=2 AND B.S EQ c.s. EQ) AS D ON Anderson, D==D.I D