Tracking merged ids back to an active account

Gawain Lavers $organization over 6 years ago

Does anyone have a nice query for matching a merged account number to the active account that it ultimately leads to (obviously an entry in T_MERGED may just lead you to another account that has since been merged) without using a loop?

Update: unless there is a good way, maybe T_MERGED should have a column called "current_id" that updates all previous rows related to the merged id with the new id.

Parents

David Frederick $organization over 6 years ago

Hi all,

I know this is an older post, but I thought some of you might find this useful for this and possibly other purposes. A recursive CTE can be used in situations like this. Here is code that will always return the most recent kept_id from t_merged regardless of the which delete_id is passed in the chain:




    
        Fullscreen
        6443.merged_cte.sql
        Download
    
    DECLARE @delete_id int = 355941;

WITH merged_cte (kept_id)
AS (
    SELECT a.kept_id
    FROM dbo.T_MERGED a
    WHERE a.delete_id = @delete_id
          AND a.status = 'S'
    UNION ALL
    SELECT a.kept_id
    FROM dbo.T_MERGED a
        INNER JOIN merged_cte b
            ON a.delete_id = b.kept_id
    WHERE a.status = 'S'
       )
SELECT a.kept_id
FROM merged_cte a
WHERE NOT EXISTS -- Make sure we only get the most recent kept_id in the chain
(
    SELECT *
    FROM dbo.T_MERGED b
    WHERE a.kept_id = b.delete_id
          AND b.status = 'S'
);

Here are some rows from our T_MERGED table that I tested against:

Kept_Id	Delete_Id
119157	355941
57053	119157
3465887	57053

As you can see, the merge chain looks like this: 355941 --> 119157 --> 57053 --> 3465887

I can set @delete_id to the above query to 355941, 119157, or 57053 and it will always return 3465887. And it runs in under a second. I hope this helps!

Thanks,
David

Reply