Solved

Delete Duplicate records

Posted on 2015-01-08
16
75 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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In this article I will describe the Copy Database Wizard 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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

747 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

15 Experts available now in Live!

Get 1:1 Help Now