Merge Query in SQL Server with conditional update

Question

I have a table with duplicate records. The table format is like this

FIRST Day input Table Name-ABC

ani         cdate
7076419812  2016-10-12 00:00:00.000
9168919394  2016-10-12 00:00:00.000
6282358407  2016-10-12 00:00:00.000
9168834643  2016-10-12 00:00:00.000

I want to insert into another table which will contains unique mdn with TS.But for the date 2016-10-12 00:00:00.000 i can easily insert into another table which has the same format.

OUTPUT Table Name-- PQR
MDN,         TS
7076419812  2016-10-12 00:00:00.000
9168919394  2016-10-12 00:00:00.000
6282358407  2016-10-12 00:00:00.000
9168834643  2016-10-12 00:00:00.000

but for date 2016-10-13 00:00:00.000 I have the same record of mixed date with same MDN and I want to update existing MDN with the new date and remaining MDN should be inserted as new records. For the second day Input table records are like

ani         cdate
7076419812  2016-10-13 00:00:00.000
9168919394  2016-10-13 00:00:00.000
6282358233  2016-10-12 00:00:00.000
9168834609  2016-10-12 00:00:00.000

the output should be like this after processing of second-day input table

   mdn          ts
   7076419812   2016-10-13 00:00:00.000  
    9168919394  2016-10-13 00:00:00.000
    6282358407  2016-10-12 00:00:00.000
    9168834643  2016-10-12 00:00:00.000
    6282358233  2016-10-12 00:00:00.000
    9168834609  2016-10-12 00:00:00.000

This is my query:--

merge PQR as lc
using (select ani, calldate from ABC  ) as st
on lc.mdn = st.ani 
WHEN MATCHED  and lc.ts < st.calldate   THEN 
update set lc.ts = st.calldate
WHEN NOT MATCHED THEN  
insert (mdn,ts) values (st.ani, st.calldate);

sorry, maybe is the format but cant see what is current output and what is the desire ouput. — Juan Carlos Oropeza, Oct 14 '16 at 20:16
look at the last table that is the desired output where mdn are unique but date has been changed. but MDN should not be repeated. — Ramdeo angh, Oct 14 '16 at 20:20
ok, and what is the current output? Maybe is obvious for you but dont have time to compare 4 different table. — Juan Carlos Oropeza, Oct 14 '16 at 20:22
That `WHEN MATCHED and lc.ts < st.calldate` seems rather dodgy. Wouldn't something like `WHEN MATCHED and dateadd(day,1,cast(lc.ts as date)) = cast(st.calldate as date)` be safer? — LukStorms, Oct 14 '16 at 20:31
current output is getting duplicate records with different date. — Ramdeo angh, Oct 14 '16 at 20:33

score 0 · Answer 1 · answered Oct 14 '16 at 21:09

0

This was too long for a comment, but posting this for others.

Your query seems fine, you need to elaborate on what isn't returning correctly for you. Here is some test data using your same logic...

if object_id ('tempdb..#PRQ') is not null drop table #PRQ
create table #PRQ (mdn bigint, ts datetime)

insert into #PRQ (mdn, ts)
values
(7076419812,'2016-10-12 00:00:00.000'),
(9168919394,'2016-10-12 00:00:00.000'),
(6282358407,'2016-10-12 00:00:00.000'),
(9168834643,'2016-10-12 00:00:00.000')



if object_id ('tempdb..#ABC') is not null drop table #ABC
create table #ABC (ani bigint, cdate datetime)

--this is simulating "day 2" where the date is changing for 7076419812 and 9168919394

insert into #ABC (ani, cdate)
values
(7076419812,'2016-10-13 00:00:00.000'),  --date change
(9168919394,'2016-10-13 00:00:00.000'),  --date change
(6282358407,'2016-10-12 00:00:00.000'),
(9168834643,'2016-10-12 00:00:00.000')

--run the merge with the same logic you used

MERGE #PRQ AS T
USING(SELECT ani, cdate FROM #ABC) AS S
ON T.mdn = s.ani
WHEN MATCHED AND t.ts < s.cdate THEN
UPDATE SET t.ts = s.cdate
WHEN NOT MATCHED THEN
INSERT (mdn,ts) VALUES (s.ani, s.cdate);

--you will see the dates changed for 7076419812 and 9168919394
SELECT * FROM #PRQ

--Now we will update it for "day 3"

update #ABC
set cdate = '2016-10-14 00:00:00.000'
where ani in (7076419812,9168919394)

--run the same merge and select the results from #PRQ

MERGE #PRQ AS T
USING(SELECT ani, cdate FROM #ABC) AS S
ON T.mdn = s.ani
WHEN MATCHED AND t.ts < s.cdate THEN
UPDATE SET t.ts = s.cdate
WHEN NOT MATCHED THEN
INSERT (mdn,ts) VALUES (s.ani, s.cdate);

--see the date changes for 7076419812 and 9168919394
SELECT * FROM #PRQ

--now add two new records with two new ani to your ABC table

insert into #ABC (ani, cdate)
VALUES
(5469358407,'2016-10-15 00:00:00.000'),
(1234834643,'2016-10-15 00:00:00.000')

--and now run the merge again, and see the added rows since they won't match

MERGE #PRQ AS T
USING(SELECT ani, cdate FROM #ABC) AS S
ON T.mdn = s.ani
WHEN MATCHED AND t.ts < s.cdate THEN
UPDATE SET t.ts = s.cdate
WHEN NOT MATCHED THEN
INSERT (mdn,ts) VALUES (s.ani, s.cdate);

SELECT * FROM #PRQ

answered Oct 14 '16 at 21:09

S3S

24,809
5
26
45

i want it to do in one shot.. you are doing manually. there can be thousand of MDN. – Ramdeo angh Oct 14 '16 at 21:39
the merge will happen in "one shot". I'm manually updating the temp table as an example... but if ABC was an actual table that gets updated via some application, trigger, etc then when ever you ran the job which executed the merge, it would update your target table PRQ. – S3S Oct 14 '16 at 21:42
Can you elaborate @Ramdeoangh – S3S Oct 14 '16 at 21:49
but this condition is not working well.sometimes I get duplicate records or sometimes merge exception " The MERGE statement attempted to UPDATE or DELETE the same row more than once." – Ramdeo angh Oct 14 '16 at 21:53
Ah. You must have more than 1 record in your ABC for a particular ani. Change the using to this... USING (SELECT ani, MAX(cdate) as cdate FROM #ABC GROUP BY ani) @Ramdeoangh – S3S Oct 14 '16 at 22:07
Yes, little bit . I am not getting exception now but if I insert records for the date 2016-10-13 and after that I am inserting the records for 2016-10-12 then here I got the problem. If MDN is already present for the date 13 then it should not be again inserted but in here it is inserting the same MDN for the date of 12 also. It should not happen. – Ramdeo angh Oct 15 '16 at 18:59
That would not happen with that merge statement that I have written. How are you in starting them? – S3S Oct 15 '16 at 19:00
It would because condition is like this t.ts < s.cdate and if ts=13 at first insert and in second insert is 12 then the condition 13<12 will not work and new records will be inserted. – Ramdeo angh Oct 15 '16 at 19:30
On the WHEN NOT MATCHED part just add AND t.ts < s.cdate or greater than which ever you want. This will prevent this. @Ramdeoangh – S3S Oct 15 '16 at 20:01
Sorry I meant add another when matched* – S3S Oct 15 '16 at 20:14

Merge Query in SQL Server with conditional update

1 Answers1