Delete Oldest Entry on a Table

Question

Hello,

I have a table that with a column that has the "InsertionDate" I would like to erase at certain time the oldest row in the table using the "InsertionDate"

I was trying to use:

DELETE MIN(InsertionDate) FROM [MyTable] WHERE UserName = '" + John + "'

But I get errors, something that I know by sure is the entry "UserName" that is NOT unic (I may have a lot of rows with "John" in column "UserName".

Thanks

---

Kikeman Electric Systems Engineer

Answer 1

You should be able to use either the row_Number or rank function to do this -- depending on what you want done with ties.  For example:

declare @test table
( userName varchar(15), insertionDate datetime );
insert into @test
select 'John', '20120223 15:00' union all
select 'John', '20120223' union all
select 'Barny Copter', '20120223';
--select * from @test;

;with delete_Target as
( select
    row_Number() over( order by insertionDate
    ) as Rn,
    userName,
    insertionDate
  from @test
  where userName = 'John'
)
--select *
delete
from delete_Target
where rn = 1;

select * from @test;
/* -------- Output: --------
userName        insertionDate
--------------- -----------------------
John            2012-02-23 15:00:00.000
Barny Copter    2012-02-23 00:00:00.000

(2 row(s) affected)
*/

Answer 2

The reason for the error is that DELETE does not allow a COLUMN to be specified between the DELETE keyword and the FROM clause. This is because only entire records are DELETEd. To specify which records, the WHERE caluse is used.

Hence, the straight rewrite of your query is:

DELETE FROM [MyTable] WHERE InsertionDate = (SELECT  MIN(InsertionDate)  FROM [MyTable] WHERE UserName = '" + John + "');

If InsertionDate is the PK (or otherwise UNIQUE) this will DELETE (at most) one record. If not, it may unexpectedly DELETE more than one record. The usual approach is to include something that is guaranteed to be UNIQUE in the WHERE clause.

Answer 3

The reason for the error is that DELETE does not allow a COLUMN to be specified between the DELETE keyword and the FROM clause. This is because only entire records are DELETEd. To specify which records, the WHERE caluse is used.

Hence, the straight rewrite of your query is:

DELETE FROM [MyTable] WHERE InsertionDate = (SELECT  MIN(InsertionDate)  FROM [MyTable] WHERE UserName = '" + John + "');

If InsertionDate is the PK (or otherwise UNIQUE) this will DELETE (at most) one record. If not, it may unexpectedly DELETE more than one record. The usual approach is to include something that is guaranteed to be UNIQUE in the WHERE clause.

If really only onerow should be deleted (never more), rowversion/timestamp might be a solution (has nothing to do with time; it's a running number of inserts, updates and deletes).
Of course using it would require to add the row (equal to binary(8) in size) and re-insert every row to get usefull values. And I am not entierly certain what happens if one update/delete affects multiple rows (it's primary use is to easily identify if the row has changed).