[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 89
  • Last Modified:

Delete Duplicate records

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
websss
Asked:
websss
  • 6
  • 5
  • 4
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
Pratima PharandeCommented:
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
 
websssAuthor Commented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Pratima PharandeCommented:
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
 
websssAuthor Commented:
error--0
Getting the above error with that code
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Pratima PharandeCommented:
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
 
websssAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Pratima PharandeCommented:
have you tried updated one
0
 
websssAuthor Commented:
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
 
GanapathiCommented:
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
 
websssAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
websssAuthor Commented:
great that works nicely!

do i not need:
DELETE data 
WHERE rn > 1

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now