merge ALMOST duplicate records IN sql

Question

Greeting all...

i have two table and their structure is identical...

Table logDetail

Date              Time         CardID        Status  
2012-01-20        00:00:00     A1            ABSENT
2012-01-20        00:00:00     B1            ABSENT
2012-01-20        00:00:00     C3            ABSENT
2012-01-20        00:00:00     D1            ABSENT

Table preStatus

Date            Time        CardID        Status
2012-01-20     07:00:10     A1            COMING
2012-01-20     07:10:00     C3            COMING
2012-01-20     08:00:00     B1            LATE
2012-01-20     17:00:00     B1            BACK
2012-01-20     17:10:10     A1            BACK
2012-01-20     17:13:00     C3            BACK

After Merge

Date
2012-01-20     07:00:10     A1            COMING
2012-01-20     07:10:00     C3            COMING
2012-01-20     08:00:00     B1            LATE
2012-01-20     00:00:00     D1            ABSENT
2012-01-20     17:00:00     B1            BACK
2012-01-20     17:10:10     A1            BACK
2012-01-20     17:13:00     C3            BACK

How may i merge these two table because in table b there is duplicate records and when i do a merge...

merge into logDetail as Target 
using preStatus as Source
on Target.L_Date=Source.L_Date
and Target.L_Time='00:00:00'
and Target.L_CardID=Source.L_CardID 
when matched then

update set Target.L_Status=Source.L_Status,
Target.L_Time=Source.L_Time
when not matched then
insert (L_Date,L_Time,L_CardID,L_Status) 
                           values(Source.L_Date,Source.L_Time,Source.L_CardID,Source.L_Status);

it said The MERGE statement attempted to UPDATE or DELETE the same row more than once

Any help is greatly appreciated.

Ben · Accepted Answer

You don't appear to want to merge the tables, since CardID is not a primary key.

It looks like you want to retain the entries where Status = "COMING" even when there are new entries where stats = "BACK".

I suggest you do this in two steps, first do inserts of the preStatus data, then delete "ABSENT" rows where there exists "COMING" and "BACK" rows.

/* Insert new data */
insert logDetail
select * from preStatus

/* Delete Absent rows where there is a COMING or BACK row for the same item on the same day */
Delete logDetail
from logDetail ld1
where 
    /* Absent rows only */
    ld1.time = '00:00:00'
and ld1.Status = 'ABSENT'
/*  And there must be a COMING or BACK row for the same card on the same day */
and exists (
   select 1 from logDetail ld2
   where ld2.Date = ld1.Date
   and ld2.CardID = ld1.CardID
   and ld2.Time > '00:00:00'
   and ld2.Status <> 'ABSENT'
)

To remove rows for the same Date, CardID, and same Status, but where there is a later Time:

Delete logDetail
from logDetail ld1
where 
ld1.status in ('COMING', 'BACK')
/*  COMING or BACK row only */ 
      /* for the same card on the same day, with a later time*/
and exists (
   select 1 from logDetail ld2
   where ld2.Date = ld1.Date      
   and ld2.CardID = ld1.CardID
   and ld2.Status = ld1.Status
   and ld2.Time > ld1.Time
)

merge ALMOST duplicate records IN sql

Answers (2)

Related Questions