none
Delete Oldest Entry on a Table

    Pertanyaan

  • 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

    23 Februari 2012 17:22

Jawaban

  • 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)
    */

    23 Februari 2012 18:18

Semua Balasan

  • 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)
    */

    23 Februari 2012 18:18
  • 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.


    24 Februari 2012 13:18
  • 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).

    01 Maret 2012 11:10