CODEWITHSUNDEEP
sqlsql-server
nav100
nav100

Reputation: 411

Delete data from table without date field

I am trying to delete the data from table older than 6 months where there is no date field. Please let me know.

Upvotes: 0

Views: 1254

Answers (3)

David R Tribble
David R Tribble

Reputation: 12214

Without a date column, you cannot know which rows are older than six months. This is especially a problem if the key column is not a monotonically increasing value (i.e., not a simple integer sequence).

However, if you have backups of the database, you might possibly generate a list of rows in the six-month-old backup dB and compare it to the rows in the current dB, and then deduce which rows were added after the backup was made. You know then that the remaining rows are at least six months old.

Suggestion #2

If the row's key column is a monotonically increasing value (such as an integer sequence, or uniquely increasing account ID), then you could insert a special sentinel record every month (or week, or day, whatever) with special values in it that tell you that it's a marker row (and not normal data). You then know that every row with a lower key field value than the sentinel's key value was inserted before the sentinel.

Then every month (week/day/whatever), finding those older rows and deleting them is fairly simple.

Upvotes: 0

Simon Hughes
Simon Hughes

Reputation: 3574

You could calculate the average of how much data per month is generated. * 6 will give you the number of rows to keep. So you could take the current max ID, less n rows. If your table does not have an ID, you can use an artifical one like this:

SELECT  *, n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
FROM    your_table_name

Upvotes: 0

mystery
mystery

Reputation: 19523

Without a date field this isn't possible.

However, if you have a backup from 6 months ago, you could delete all data from the table that was in that backup.

Upvotes: 6

Related Questions