CODEWITHSUNDEEP
sql-server
asmgx
asmgx

Reputation: 8034

How to fill ParentID column in one update statement?

My table is like this:

ID  Code  ParentID
-------------------
1   A01    NULL
2   B83    NULL
3   H92    NULL
15  A013   NULL
23  A018   NULL
33  A01899 NULL
44  B8329  NULL
67  B83293 NULL

What I want is to update ParentID to match the ID of the parent code.

and so on.

You can see the length of A01 is 3 while the child A013 length is 4, the length of A018 is 4 and the child A01899 length is 6.

I can do that with multiple update statements and repeat that for each case.

UPDATE A
SET ParentID = B.ID
FROM Table A 
INNER JOIN Table B ON A.Code like B.Code + '%'
WHERE LEN(A.Code) = 4 AND LEN(B.Code) = 3

But the question is how to do that in a single update statement?

Upvotes: 1

Views: 58

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81990

Just another option.

Significant Digits can be a risky business in the long run. It has been my experience that they tend to have a rather short shelf-life until an exception needs to be made.

In the example below, we allow for up to three characters in distance. We apply the closest first via the coalesce()

Just to be clear, Left Join D may not be necessary if Parents are within 1 or 2 characters. Conversely, this could be expanded if needed Left Join D ...

Example

Declare @YourTable Table ([ID] int,[Code] varchar(50),[ParentID] int)
Insert Into @YourTable Values 
 (1,'A01',NULL)
,(2,'B83',NULL)
,(3,'H92',NULL)
,(15,'A013',NULL)
,(23,'A018',NULL)
,(33,'A01899',NULL)
,(44,'B8329',NULL)
,(67,'B83293',NULL)

;with cte as ( 
Select A.*
      ,PtNr = coalesce(B.ID,C.ID,D.ID) 
 From  @YourTable A
 Left Join @YourTable B on left(A.[Code],len(A.[Code])-1)=B.[Code]
 Left Join @YourTable C on left(A.[Code],len(A.[Code])-2)=C.[Code] 
 Left Join @YourTable D on left(A.[Code],len(A.[Code])-3)=D.[Code] 
)
Update cte set ParentID=PtNr

Select * From @YourTable

The Update Table

ID  Code    ParentID
1   A01     NULL
2   B83     NULL
3   H92     NULL
15  A013    1
23  A018    1
33  A01899  23
44  B8329   2
67  B83293  44

Upvotes: 2

seanb
seanb

Reputation: 6685

You can first find all the relevant matches - similar to what you have - but also the length of the code it matched to. Then find the one with the longest matched code.

CREATE TABLE #TableA (ID int, Code varchar(10), ParentID int);
INSERT INTO #TableA (ID, Code, ParentID)
VALUES
(1 ,  'A01'   , NULL),
(2 ,  'B83'   , NULL),
(3 ,  'H92'   , NULL),
(15,  'A013'  , NULL),
(23,  'A018'  , NULL),
(33,  'A01899', NULL),
(44,  'B8329' , NULL),
(67,  'B83293', NULL);


WITH A AS
(SELECT TA.ID, TA.ParentID, TB.ID AS TB_ID,
        ROW_NUMBER() OVER (PARTITION BY TA.ID ORDER BY TB.Len_Code DESC) AS rn
  FROM #TableA TA
       INNER JOIN 
          (SELECT ID, Code, LEN(CODE) AS Len_Code
             FROM #TableA
          ) TB ON TA.Code LIKE TB.Code + '%'
  WHERE TA.ID <> TB.ID
) 
UPDATE  A
  SET   A.ParentId = A.TB_ID
  WHERE A.rn = 1;

Result

ID  Code    ParentID
1   A01     NULL
2   B83     NULL
3   H92     NULL
15  A013    1
23  A018    1
33  A01899  23
44  B8329   2
67  B83293  44

Upvotes: 3

Related Questions