Update parent id Hierarchy - SQL Server

Question

I have a table with a column with the account IDs and I need to create a hierarchy scheme. So I created a column to add the parent account IDs, but I do not know how to update dynamically.

I updated some records manually to find out how it would look, how could I make a script to update the other almost 2,000 lines?

Answer 1

You can use a RECURSIVE query for this job:

CREATE TABLE accounts(account varchar(10), account_parent varchar(10), updated_field int);
INSERT INTO accounts VALUES
('10000', NULL, 0),
('10001','10000', 0),
('10002','10001', 0),
('10003','10002', 0),
('10004','10003', 0),
('10005','10004', 0),
('10006','10005', 0),
('10007', NULL, 0),
('10008', NULL, 0),
('10009', NULL, 0),
('10010', NULL, 0),
('10011', NULL, 0);

GO

12 rows affected

;WITH tree AS
(
    SELECT c1.account, c1.account_parent, Level = 1
    FROM dbo.[accounts] c1
    WHERE c1.account = '10000'
    UNION ALL
    SELECT c2.account, c2.account_parent, Level = Level + 1
    FROM dbo.[accounts] c2 INNER JOIN tree ON tree.account = c2.account_parent
)
UPDATE accounts
SET    updated_field = Level
FROM   tree
INNER  JOIN accounts
ON     accounts.account = tree.account
OPTION (MAXRECURSION 2000)
;
GO

7 rows affected

SELECT * FROM accounts;
GO

account | account_parent | updated_field
:------ | :------------- | ------------:
10000   | null           |             1
10001   | 10000          |             2
10002   | 10001          |             3
10003   | 10002          |             4
10004   | 10003          |             5
10005   | 10004          |             6
10006   | 10005          |             7
10007   | null           |             0
10008   | null           |             0
10009   | null           |             0
10010   | null           |             0
10011   | null           |             0

Keep in mind you must set a MAXRECURSION value greater enough to get all updated records.

Updating records where parent is null

To update the rest of the records where account_parent is null I've used a CTE function with LAG().

Note the CTE uses WHERE account >= '10006' to flag the last record of the previous tree structure. The following records must have account_parent = null

WITH upd AS
(
    SELECT   account, account_parent,
             LAG(account) OVER (ORDER BY account) AS prev_account
    FROM     accounts
    WHERE    account >= '10006'    -- this is last element of the tree
)
UPDATE     acc
SET        account_parent = upd.prev_account
FROM       accounts acc
INNER JOIN upd
ON         upd.account = acc.account
WHERE      upd.prev_account IS NOT NULL
;
GO

5 rows affected

This is the final result:

SELECT * FROM accounts;
GO

account | account_parent | updated_field
:------ | :------------- | ------------:
10000   | null           |             1
10001   | 10000          |             2
10002   | 10001          |             3
10003   | 10002          |             4
10004   | 10003          |             5
10005   | 10004          |             6
10006   | 10005          |             7
10007   | 10006          |             0
10008   | 10007          |             0
10009   | 10008          |             0
10010   | 10009          |             0
10011   | 10010          |             0

dbfiddle here