Solved

Delete data from a table

Posted on 2016-10-03
25
54 Views
Last Modified: 2016-10-25
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)
0
Comment
Question by:websss
  • 8
  • 7
  • 6
  • +1
25 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
 

Author Comment

by:websss
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
You're right Paul. That's why knee jerk reaction is not good :)
0
 

Author Comment

by:websss
Comment Utility
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
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Do you need more help on this?
0
 

Author Comment

by:websss
Comment Utility
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
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
 

Author Comment

by:websss
Comment Utility
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
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
If you want to leave one records then count must be required.

If above query is working you can use.
0
 

Author Comment

by:websss
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Yes you are right it is row number , In that case we can leave the first row.  :)
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
@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
 

Author Comment

by:websss
Comment Utility
still  not right

I only want to delete DEVICEID data if the device hasn't reported in last 3 months
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 125 total points
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Oh... I  may still be wrong... sorry
0
 

Author Comment

by:websss
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 

Author Comment

by:websss
Comment Utility
Hi Vitor
Nearly there

i'm geting
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'inner'.
0
 
LVL 16

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 125 total points
Comment Utility
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
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 250 total points
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
websss, a feedback will be appreciated.
Cheers
1

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

728 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