SQL Query to Prune database

Hi,

I have a database with 2 tables that needs some data pruning. There is a list of meter in a table called 'TL'. I first need to select all items where the column 'SampleInterval' = 6000.

There is another table called 'TLData' where all of the data is stored for each meter. This has columns 'Timestamp', 'TLInstance' and 'Data'. I need to delete all records, where 'TLInstance' is within the list returned from the previous query and 'Timestamp' is older than 2 years.

TL Table:

ID - Name - SampleInterval - TLInstance
 1      Test          6000                    1000

TLData:

ID - Timestamp             -     Data - TLInstance
1       '2010-8-10 6:00'          123         1000

Can anyone help with this?
LVL 1
wint100Asked:
Who is Participating?
 
Russ SuterCommented:
Not 100% certain of your original query but something like this...
DELETE FROM
  TLData
WHERE
  Timestamp < DATEADD(YEAR, -2, GETDATE())
  AND TLInstance IN
  (
  SELECT
    TLInstance
  FROM
    TLTable
  WHERE
    SampleInterval = 6000
  )

Open in new window

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.