Solved

Delete Duplicate records

Posted on 2015-01-08
16
85 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
  • +1
16 Comments
 
LVL 143

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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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 143

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
 
LVL 143

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 143

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 143

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Detach & Attach 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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

733 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