1

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?

enter image description here

1
  • How do you select records to be updated, by 'Account' or by 'AccountParent'? – McNets Jun 28 '17 at 13:03
3

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

4
  • The script worked, but how can I do to fill null hierarchies? – Rafael Lima Jun 28 '17 at 19:49
  • @Rafaells with what values? – McNets Jun 28 '17 at 20:16
  • In the account_parent column, it has null values after 10005. How could we continue to populate this column with its parent accounts? – Rafael Lima Jun 28 '17 at 20:22
  • ok, let me prepare an answer, it requires another sentence. – McNets Jun 28 '17 at 20:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.