Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Delete data from a table

Posted on 2016-10-03
25
Medium Priority
?
65 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 52

Expert Comment

by:Vitor Montalvão
ID: 41826120
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 49

Expert Comment

by:PortletPaul
ID: 41826137
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 49

Expert Comment

by:PortletPaul
ID: 41826147
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:websss
ID: 41826150
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 52

Expert Comment

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

Author Comment

by:websss
ID: 41826158
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 35

Expert Comment

by:Pawan Kumar
ID: 41826166
Do you need more help on this?
0
 

Author Comment

by:websss
ID: 41826171
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 35

Expert Comment

by:Pawan Kumar
ID: 41826178
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
ID: 41826189
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 35

Expert Comment

by:Pawan Kumar
ID: 41826193
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
ID: 41826195
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
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41826198
Yes you are right it is row number , In that case we can leave the first row.  :)
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41826201
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 35

Expert Comment

by:Pawan Kumar
ID: 41826210
@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
ID: 41826228
still  not right

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

Expert Comment

by:Vitor Montalvão
ID: 41826239
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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 500 total points
ID: 41826306
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 49

Expert Comment

by:PortletPaul
ID: 41826310
Oh... I  may still be wrong... sorry
0
 

Author Comment

by:websss
ID: 41826357
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 52

Expert Comment

by:Vitor Montalvão
ID: 41826372
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
ID: 41826420
Hi Vitor
Nearly there

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

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 500 total points
ID: 41826427
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 52

Accepted Solution

by:
Vitor Montalvão earned 1000 total points
ID: 41826458
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 52

Expert Comment

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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

886 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