Update parent id Hierarchy - SQL Server
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 &gt;= '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 &gt;= '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