Delete data from a table

Hi, I have 2 tables which are essentially:

1 DeviceMaster
2 DeviceLog

I need to delete all but the latest record from the LOGs if the DEVICE hasn't reported any log data in the last 3 months

This bit of code returns the asset ID (IMEI number) for anything that hasn't reported in the last 3 months:
select ImeiNumber, [Name]
from [wlt_tblDevices] d
left join [dbo].[wlt_tblAssets] a on d.[ifk_AssignedAssetId] = a.Id
where not exists (select 1 
             from [tblCommonTrackingData] c 
              where d.ImeiNumber =  c.vpkDeviceId
                    and c.dGPSDateTime between '2016/07/01' and '2016/10/01')

Open in new window


What I need to do is delete all this data from tblCommonTrackingData (apart from the last recorded ID for that asset (tblCommonTrackingData .ID)

OR event better,  just use the 1 table tblCommonTrackingData and delete all but latest record if that device hasn't reported in the last 3 months (not sure how this would work though)
websssAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Sorry, I forgot to remove the 'from' keyword after the inner join:
delete from [tblCommonTrackingData]
	inner join [wlt_tblDevices] d 
		left join [dbo].[wlt_tblAssets] a on d.[ifk_AssignedAssetId] = a.Id
	on d.ImeiNumber =  [tblCommonTrackingData].vpkDeviceId
where [tblCommonTrackingData].dGPSDateTime < (SELECT MAX(dGPSDateTime) 
						FROM [tblCommonTrackingData] t2
						WHERE [tblCommonTrackingData].vpkDeviceId = t2.vpkDeviceId
						 AND t2.dGPSDateTime <= '2016/07/01')

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this in a TEST environment first to check if is deleting all the expected records:
delete
from [wlt_tblDevices] d
left join [dbo].[wlt_tblAssets] a on d.[ifk_AssignedAssetId] = a.Id
where not exists (select 1 
             from [tblCommonTrackingData] c 
              where d.ImeiNumber =  c.vpkDeviceId
                    and c.dGPSDateTime between '2016/07/01' and '2016/10/01')

Open in new window

0
 
PortletPaulfreelancerCommented:
1 DeviceMaster = wlt_tblDevices
2 DeviceLog = tblCommonTrackingData

and you want to delete from 2 (the logs)

I would't use the query above (sorry Vitor, but I think that query deletes from 1 [the device master])
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
PortletPaulfreelancerCommented:
I think you want to delete from tblCommonTrackingData
Try this as a select first:
WITH CTE
      AS (
            SELECT
                  *
                , ROW_NUMBER() OVER (PARTITION BY vpkDeviceId ORDER BY dGPSDateTime DESC) AS rn
                , MAX(dGPSDateTime) OVER (PARTITION BY vpkDeviceId)                       AS max_dt
            FROM tblCommonTrackingData c
      )
SELECT *
FROM CTE
WHERE max_dt < '20160701'
      OR (max_dt >= '20160701'  AND rn > 1)
;

Open in new window

That should display the rows you would delete.

Then, if that is OK
WITH CTE
      AS (
            SELECT
                  *
                , ROW_NUMBER() OVER (PARTITION BY vpkDeviceId ORDER BY dGPSDateTime DESC) AS rn
                , MAX(dGPSDateTime) OVER (PARTITION BY vpkDeviceId)                       AS max_dt
            FROM tblCommonTrackingData
      )
DELETE FROM CTE
WHERE max_dt < '20160701'
      OR (max_dt >= '20160701'  AND rn > 1)
;

Open in new window

as always, have some way to restore if you have made an error (or I have)
0
 
websssAuthor Commented:
Thanks Paul

Your query looks more like it as Vitors did delete from wrong table

However, when i'm looking at the SELECT, i'm seeing dates such as 2016-09-28 23:33:45.000

I should only be seeing dates below '20160701' right?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You're right Paul. That's why knee jerk reaction is not good :)
0
 
websssAuthor Commented:
also, i think rn is just the ID of the record, so i'm not sure its will keep the 1 record (last record?)
0
 
Pawan KumarDatabase ExpertCommented:
Do you need more help on this?
0
 
websssAuthor Commented:
yes, I think this query is closer to what i need:

WITH CTE
      AS (
            SELECT
                  *
                , ROW_NUMBER() OVER (PARTITION BY vpkDeviceId ORDER BY dGPSDateTime DESC) AS rn
                , MAX(dGPSDateTime) OVER (PARTITION BY vpkDeviceId)                       AS max_dt
            FROM tblCommonTrackingData c
      )
SELECT *
FROM CTE
WHERE (max_dt <= '20160701' AND rn > 1)
;

Open in new window


However, I dont believe it will keep at least 1 record in this table.
I think it will just SELECT (will alter to DELETE) ALL DATA for units that havent LOGGED in the last 3 months?
0
 
Pawan KumarDatabase ExpertCommented:
Try this ..

A minor modification....

WITH CTE AS 
(
			SELECT * FROM 
			(
				SELECT
					  *
					, ROW_NUMBER() OVER (PARTITION BY vpkDeviceId ORDER BY dGPSDateTime DESC) AS rn
					, MAX(dGPSDateTime) OVER (PARTITION BY vpkDeviceId)                       AS max_dt
				FROM tblCommonTrackingData c
			)p
			WHERE (p.max_dt <= '20161003')
)
DELETE FROM CTE
WHERE rn > 1

Open in new window

0
 
websssAuthor Commented:
Thanks

Actually, the more i look at it the more I think this could be wrong

I'm not sure i explained myself well enough

how about this

Delete all data from tblCommonTrackingData
If the tblCommonTrackingData.vpKDeviceID doesn't have any data in the last 3 months?


i.e.
Delete from tblCommonTrackingData
where not exists (select *
             from [tblCommonTrackingData] c
               where c.dGPSDateTime between '2016/07/01' and '2016/10/01')
--however, leave at least 1 record
0
 
Pawan KumarDatabase ExpertCommented:
If you want to leave one records then count must be required.

If above query is working you can use.
0
 
websssAuthor Commented:
Thanks,

However RN refers to row number and not count?
Or am i mistaken?

also your query doesn't seem to mention the 3 months ago thing
0
 
Pawan KumarDatabase ExpertCommented:
Yes you are right it is row number , In that case we can leave the first row.  :)
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this version. It should work if dGPSDateTime stores date and time and not only date:
delete from [tblCommonTrackingData]
where dGPSDateTime between '2016/07/01' and '2016/10/01' 
	and dGPSDateTime < (SELECT MAX(dGPSDateTime) FROM [tblCommonTrackingData])

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
@Author - Now check the logic given by Vitor, In this case also we are deleting all values between the date range and Just leaving the maximum date from the dataset.

I hope it helps.
0
 
websssAuthor Commented:
still  not right

I only want to delete DEVICEID data if the device hasn't reported in last 3 months
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
It misses the joins:
delete from [tblCommonTrackingData]
	inner join from [wlt_tblDevices] d 
		left join [dbo].[wlt_tblAssets] a on d.[ifk_AssignedAssetId] = a.Id
	on d.ImeiNumber =  [tblCommonTrackingData].vpkDeviceId
where [tblCommonTrackingData].dGPSDateTime between '2016/07/01' and '2016/10/01' 
	and [tblCommonTrackingData].dGPSDateTime < (SELECT MAX(dGPSDateTime) 
						FROM [tblCommonTrackingData] t2
						WHERE [tblCommonTrackingData].vpkDeviceId = t2.vpkDeviceId)

Open in new window

0
 
PortletPaulConnect With a Mentor freelancerCommented:
Afraid I  have missed all the fun.

My original query was designed to retain only the MOST RECENT row for each device if that device had been referenced at all after the given date.

That is why you saw dates after the given date, because they were not the most recent reference. (When rn=1 that is the most recent row)

I believed that logic was required in the original question.

I think this does want you have now described:

Delete everything before the given date but leave at least one row. This approach will retain the most recent row if the max date is less than the given date. Otherwise it keeps all rows after the given date. Hope this helps.
WITH CTE
      AS (
            SELECT
                  *
                , ROW_NUMBER() OVER (PARTITION BY vpkDeviceId ORDER BY dGPSDateTime DESC) AS rn
                , MAX(dGPSDateTime) OVER (PARTITION BY vpkDeviceId)                       AS max_dt
            FROM tblCommonTrackingData c
      )
SELECT *
FROM CTE
WHERE max_dt < '20160701'
         AND rn > 1
;

Open in new window

I'm only on a phone right now and about to go to sleep so unlikely to get back for several hours.
0
 
PortletPaulfreelancerCommented:
Oh... I  may still be wrong... sorry
0
 
websssAuthor Commented:
Thanks all

Vitor, your script is returning data between '2016/07/01' and '2016/10/01'
which is wrong

We can ignore the tblAssets Table for now, please just focus on [wlt_tblDevices] & [tblCommonTrackingData]

What i need is something like this

First get all the Devices are in [wlt_tblDevices].ImeiNumber

1. Return all DeviceIDs
([wlt_tblDevices].ImeiNumber = tblCommonTrackingData.vpkDeviceId)
that have NOT reported to tblCommonTrackingData between '2016/07/01' and '2016/10/01'

2. for each of these ID's (tblCommonTrackingData.vpkDeviceId)
delete all data in tblCommonTrackingData
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
It's hard to work with no sample data so we're trying to guess what your data should look alike.
If I understand well your requirements then check if this solves your problem:
delete from [tblCommonTrackingData]
	inner join from [wlt_tblDevices] d 
		left join [dbo].[wlt_tblAssets] a on d.[ifk_AssignedAssetId] = a.Id
	on d.ImeiNumber =  [tblCommonTrackingData].vpkDeviceId
where [tblCommonTrackingData].dGPSDateTime < (SELECT MAX(dGPSDateTime) 
						FROM [tblCommonTrackingData] t2
						WHERE [tblCommonTrackingData].vpkDeviceId = t2.vpkDeviceId
						 AND t2.dGPSDateTime <= '2016/07/01')

Open in new window

0
 
websssAuthor Commented:
Hi Vitor
Nearly there

i'm geting
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'inner'.
0
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Try this..

delete from c
FROM [tblCommonTrackingData] c
	inner join [wlt_tblDevices] d ON c.ImeiNumber = c.vpkDeviceId
		left join [dbo].[wlt_tblAssets] a on d.[ifk_AssignedAssetId] = a.Id
	on d.ImeiNumber =  c.vpkDeviceId
where c.dGPSDateTime < (SELECT MAX(dGPSDateTime) 
						FROM [tblCommonTrackingData] t2
						WHERE c.vpkDeviceId = t2.vpkDeviceId
						 AND t2.dGPSDateTime <= '2016/07/01')

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
websss, a feedback will be appreciated.
Cheers
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.