CODEWITHSUNDEEP
sqlsql-servermergerows
Denarius
Denarius

Reputation: 45

sql merge two rows into one, same table. (update 1st with 2nd row data, then delete 2nd row)

I would like to merge two rows into one and then delete the obsolete row. Here is a small example of what I mean.

Initial data:

id | part A | part B
0  |  OK    |  NULL
1  | NULL   |  OK

merge 1 into 0 and then delete 1 resulting in the following.

id | part A | part B
0  |  OK    |  OK

What's the best way. There must be some kind of merge/combine function I'm overlooking.

Thanks in advance.

PS. Might be a duplicate of this: possible duplicate But it didn't get a good answer.

Edit: I'm using MS SQL (Server 2012)

Upvotes: 2

Views: 5944

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

This solves your problem - it may be possible to simplify it but I've taken it as a challenge to do it as a single statement.

declare @t table (id int not null, A varchar(20) null,
                  B varchar(19) null, C varchar(18) null)

//Sample data expanded from question
insert into @t (id,A,B,C) values
(0,'OKA',null,'Old'),
(1,null,'OKB','New')

//Input provided - @FromID is the row we'll delete
declare @FromID int
declare @ToID int

select @FromID = 1, @ToID = 0

//The actual query
;With src as (
    select @ToID as ID,A,B,C,0 as Del from @t where id = @FromID
    union all
    select @FromID,null,null,null,1
)
merge into @t t
using (select ID,A,B,C,Del from src) s
on
    t.ID = s.ID
when matched and Del = 0 then
    update set
        A = COALESCE(s.A,t.A),
        B=  COALESCE(s.B,t.B),
        C = COALESCE(s.C,t.C)
when matched then
    delete
;

//And show the final result
select * from @t

And the result is:

id          A                    B                   C
----------- -------------------- ------------------- ------------------
0           OKA                  OKB                 New

Which shows that NULLs haven't over-written non-NULLs and that, where both rows have data, we've taken the value from the row that we're deleting.

This works by constructing two rows in the src CTE - one row contains all of the data from the row we're going to remove except with the row we're copying data to as its ID column. The second row just contains the row to remove's ID value. But the rows also differ in a new column called Del which indicates whether this is the row that causes deletion (1) or an update (0).

Then, in the merge we end up matching both rows and using the Del column to decide which action to take.

Upvotes: 2

Sheshnath
Sheshnath

Reputation: 3393

in case of oracle use sub-query for conditional delete.

delete from tablename where (partA|partB)=(select partA| partB from tablename)

Upvotes: 1

Related Questions