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