Merge rows in SQL

Question

Post reply

Merge rows in SQL

Neils

SSC Veteran

Points: 271

October 28, 2024 at 2:51 pm

#4474589

I have a table like below

ID Ident     source val  opendate   closedate IsActive
--------------------------------------------------------
101 euid01   File01 x010 01-01-2023 01-01-2999    1
101 euid02   File01 x111 01-01-2023 01-01-2999    1
101 euid02   File01 x222 01-01-2023 01-10-2024    0
102 euid01   File11 x010 01-01-2023 01-01-2999    1
102 euid02   File12 x333 01-01-2023 01-01-2999    1
102 euid02   File10 x444 01-01-2023 01-10-2024    0

I want to merge it as below using a function or procedure or a block

by passing two values eg. 101,102

If the ident and val are same for two different IDs ignore that rec during merge, closedate can be get sysdate or getdate in case of terming them out.

All the records in 102 will be termed out except which has Ident and val same, in this case we ignore that record. Also find the create table script in code.

ID Ident   source val  opendate   closedate IsActive
-----------------------------------------------------------
101 euid01 File01 x010 01-01-2023 01-01-2999 1 
101 euid02 File01 x111 01-01-2023 01-01-2999 1
101 euid02 File01 x222 01-01-2023 01-10-2024 0
101 euid02 File12 x333 01-01-2023 22-10-2024 0
101 euid02 File10 x444 01-01-2023 22-10-2024 0



CREATE TABLE MDM (
ID            INT,
Ident         VARCHAR(20),
source        VARCHAR(20),
val           VARCHAR(20),
opendate      datetime,
closedate     datetime,
Isactive      bit)

insert into MDM (ID,Ident,source ,val,opendate,closedate,Isactive) 
VALUES
(101, 'euid01' ,'File01', 'x010', '01-01-2023', '01-01-2999' ,1),
(101, 'euid02' ,'File01', 'x111', '01-01-2023', '01-01-2999' ,1),
(101, 'euid02' ,'File01', 'x222', '01-01-2023', '01-10-2024' ,0),
(102, 'euid01' ,'File01', 'x010', '01-01-2023', '01-01-2999' ,1),
(102, 'euid02' ,'File02', 'x333', '01-01-2023', '01-01-2999' ,1),
(102, 'euid02' ,'File10', 'x444', '01-01-2023', '01-10-2024' ,0)

This topic was modified 3 weeks, 3 days ago by Neils.
This topic was modified 3 weeks, 3 days ago by Neils.
This topic was modified 3 weeks, 2 days ago by Neils.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

What does 'termed out' mean?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Neils SSC Veteran Points: 271 More actions · Answer 2

Termed out means making isActive =0, which is no longer active, I am trying something like below, which not yet complete, requires cleaning and fixes

PROCEDURE merge_by_id (@id_from INT, @id_to INT) as
begin
declare @vFrom INT = @id_from
declare @vInto INT = @id_to
;With cte AS
(SELECT ROW_NUMBER() OVER (ORDER BY ID,identifier, source, val, opendate) AS  rowid, 
  ID,identifier, source, val, opendate,closedate,isActive FROM MDM)
--SELECT * FROM CTE;
MERGE INTO CTE t
USING (SELECT ROW_NUMBER() OVER (ORDER BY ID,identifier, source, val, opendate) AS  rw, identifier, source, val, opendate, 
       CASE WHEN (identifier,val) IN (SELECT identifier,val FROM MDM WHERE id = @vInto) THEN 1 END AS delete_flag 
      FROM MDM WHERE id = @vFrom) s
    ON (t.rowid = s.rw)
WHEN MATCHED
    THEN
        UPDATE SET id = @vInto, closedate = getdate(), isActive = 0
        DELETE WHERE delete_flag = 1;
END

Ed B Hall of Fame Points: 3666 More actions · Answer 3

Is this an accurate description? you want to update all rows where ID = 102 (set id = 101, Isactive = 0, closedate = getdate()) unless the combination of ident and val already exists with id = 101, in which case you want to delete the row?

Does this do what you want? Note that the third row in the insert statement is for id 102, but the image shows 101. I know it's not a merge, but I'm trying to get the rules correct first.

DECLARE @RetainID INT =101,
        @MergeID INT = 102

DELETE a
--SELECT *
FROM MDM AS a
WHERE a.ID = @MergeID
AND EXISTS (SELECT 1 FROM mdm
            WHERE ID = @RetainID
            AND Ident = a.Ident
            AND val = a.Val)

UPDATE a
SET a.ID = @RetainID,
    a.Isactive = 0,
    a.Closedate = GETDATE()
FROM mdm AS a
WHERE a.ID = @MergeID
-- Not necessary after the delete 
AND NOT EXISTS (SELECT 1 FROM mdm
            WHERE ID = @RetainID
            AND Ident = a.Ident
            AND val = a.Val)

Neils SSC Veteran Points: 271 More actions · Answer 4

Note that the third row in the insert statement is for id 102, but the image shows 101

you are correct, I have edited it to correct and make it 101

Neils SSC Veteran Points: 271 More actions · Answer 5

If you delete first you will lose the data, we need to first update and then delete

Ed B Hall of Fame Points: 3666 More actions · Answer 6

The delete removes rows for id 102 that share Ident and val with 101. Should the 101, euid01, x010 row be updated in some way?