Solved

Delete Duplicate records

Posted on 2015-01-08
16
78 Views
Last Modified: 2015-01-09
Hi
I have the following


The selected lines have the same time, and the same vpkDeviceId and one is vReportId 7 and the other is 8

basically, if the following condition is met, i need to delete the both records
where GpsDate = Same
AND vpkDevice =  Same
And Where one record is vReportId 7 and the other record is vReportId 8

i'm a little confused on how to do this?

here is the select code for the above
        select dGPSDateTime,vpkDeviceID,vTextMessage,vLatitude,vLongitude,vReportID
        FROM tblCommonTrackingData
        
        where
		vReportID in (7,8)
        ORDER BY dGPSDateTime DESC

Open in new window

0
Comment
Question by:websss
  • 6
  • 5
  • 4
  • +1
16 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40539653
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 40539658
you need to delete both records if there are 2 same kind of records with condition

where GpsDate = Same
AND vpkDevice =  Same

is it correct ? then try this
Delete from tblCommonTrackingData A
inner join
 ( select dGPSDateTime,vpkDeviceID
        FROM tblCommonTrackingData
       Group by dGPSDateTime,vpkDeviceID
having  Count(*) > 1 ) X

on A.dGPSDateTime = X.dGPSDateTim and A.vpkDeviceID = X.vpkDeviceID
0
 

Author Comment

by:websss
ID: 40539664
its a little bit more complicated that that

as well as this
where GpsDate = Same
AND vpkDevice =  Same

it also needs to apply this:
AND vReportID in (7,8)
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 40539688
Delete from tblCommonTrackingData A
inner join
 ( select dGPSDateTime,vpkDeviceID
        FROM tblCommonTrackingData
where vReportID in (7,8)
       Group by dGPSDateTime,vpkDeviceID
having  Count(*) > 1 ) X

on A.dGPSDateTime = X.dGPSDateTim and A.vpkDeviceID = X.vpkDeviceID
0
 

Author Comment

by:websss
ID: 40539694
error--0
Getting the above error with that code
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40539711
please consider reading the article...

do you want to delete "ALL" the duplicated records, or maintain 1 of them in the end?
if you apply the delete with the join, it will delete all the duplicates, and none of the records will be maintained.
which is very likely not what you want to do...

in the article I linked, there is a WITH inline_query as ( ... ) DELETE WHERE rn > 1  syntax that will do what you want.

first, check of the data returned is really what you want to delete (rn > 1)
WITH data as ( select GpsDate , vpkDevice , row_number() over ( partition by GpsDate , vpkDevice order by dGPSDateTime) rn
        FROM tblCommonTrackingData
    where vReportID in (7,8)
     )
select * from data 
-- WHERE rn > 1 -- uncomment to see only the rows that would be deleted...

Open in new window


WITH data as ( select GpsDate , vpkDevice , row_number() over ( partition by GpsDate , vpkDevice order by dGPSDateTime) rn
        FROM tblCommonTrackingData
    where vReportID in (7,8)
     )
DELETE data 
WHERE rn > 1

Open in new window


eventually, you want another "order by" in the row_number() function...
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 40539712
Delete

 from tblCommonTrackingData  As A
inner join
 ( select dGPSDateTime,vpkDeviceID
        FROM tblCommonTrackingData
where vReportID in (7,8)
       Group by dGPSDateTime,vpkDeviceID
having  Count(*) > 1 ) As  X

on A.dGPSDateTime = X.dGPSDateTim and A.vpkDeviceID = X.vpkDeviceID and X.vReportID in (7,8)
0
 

Author Comment

by:websss
ID: 40539745
yes i need to delete all duplicate records

Essentially this is a journey that a lorry takes, sometimes false journeys are generated.... we can tell this from identical start and end times... so we just want to delete these records
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40539746
then you can still apply the same technique:

WITH data as ( select GpsDate , vpkDevice , row_number() over ( partition by GpsDate , vpkDevice order by dGPSDateTime) rn
        FROM tblCommonTrackingData
    where vReportID in (7,8)
     )
DELETE data
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 40539748
have you tried updated one
0
 

Author Comment

by:websss
ID: 40539758
yes i tried yours pratima
However, i get

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'As'.
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'As'.
0
 
LVL 8

Expert Comment

by:Ganapathi
ID: 40539769
See if this is the one you wanted:

DELETE
	tblCommonTrackingData
FROM
	tblCommonTrackingData T1
WHERE
   EXISTS(SELECT 1
		   FROM
		      tblCommonTrackingData T2
		   WHERE
		      T2.GpsDate = T1.GpsDate
		   AND T2.vpkDevice = T1.vpkDevice
		   AND ((T1.vReportId = 7 AND T2.vReportId = 8) OR
			   (T1.vReportId = 8 AND T2.vReportId = 7)))

Open in new window

0
 

Author Comment

by:websss
ID: 40539770
Guy, your solution seems to work!
however, one question

vreportId 7 = engine on
vReportid8 = engine off

With your query, does it look for Both?
I.e. if i got two 7's would it still show in the results?
I'm specifically looking for one 7 and one 8 at the same time
if its finds two 7's or two 8's it will cause issues

?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40539779
if you need both 7 and 8 to be there, it's different....

WITH data as ( select GpsDate , vpkDevice , row_number() over ( partition by GpsDate , vpkDevice order by dGPSDateTime) rn
        FROM tblCommonTrackingData t
    where vReportID in (7,8)
     and exists  ( select null from tblCommonTrackingData o
                              where o.GpsDate = t.GpsDate  and o.vpkDevice = t.vpkDevice 
                                    and o.vReportID in (7,8)
                                    and o.vReportID <> t.vReportID 
                     )
     )
DELETE data 

Open in new window

0
 

Author Comment

by:websss
ID: 40539986
great that works nicely!

do i not need:
DELETE data 
WHERE rn > 1

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40539994
no, because you said you want to delete both records (as the 2 together are the duplicates you want to delete)
in your case, the "duplicates" refers to the couple [one record with vReportID = 7, the other with vReportID = 8 ]

usually, from the "duplicates", one wants to delete all but one ( WHERE RN > 1  to skip the "first" one)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now