Delete Oldest Records from Database

Delete oldest records from database

If you use an auto-increment field, you can easily write this to delete the oldest 100 records:

DELETE FROM mytable WHERE id IN (SELECT id FROM mytable ORDER BY id ASC LIMIT 100)

Or, if no such field is present, use ROWID:

DELETE FROM mytable WHERE ROWID IN (SELECT ROWID FROM mytable ORDER BY ROWID ASC LIMIT 100)

Or, to leave only the latest 1000 records:

DELETE FROM mytable WHERE ROWID IN (SELECT ROWID FROM mytable ORDER BY ROWID DESC LIMIT -1 OFFSET 1000)

want to delete oldest record when a new one is added leaving no more then 5 records

You lack the selection of the user in your second query.

Also, as Jorge Campos underlines, you should order by date so that an edit/update of existing message is considered as a latest message and not an old one.

Try

INSERT INTO sample(user_id,message,created)
VALUES('abc','message6',now());

DELETE FROM sample WHERE user_id = 'abc' AND id NOT IN ( 
  SELECT id 
  FROM ( 
    SELECT id 
    FROM sample WHERE user_id = 'abc'
    ORDER BY created DESC 
    LIMIT 5
    ) x 
  ); 

How to delete records from database table older than 30 days with based on date column

If the format is yyyy/MM/dd you must change it to yyyy-MM-dd, because this is the only valid comparable format for dates in SQLite:

DELETE FROM table1 WHERE REPLACE(date_column, '/', '-') < date('now', '-30 day')

Or even better update the table, so date_column is in the proper format and you don't need the function REPLACE() every time you want to compare dates:

UPDATE table1
SET date_column = REPLACE(date_column, '/', '-')

MS SQL Delete records older than month

If there is not an existing index with a first column of [DateTimeUtc], you might try adding one. Indexing the column in the search criteria has improved mass delete performance on some of our databases. The trade-off is that inserts and updates may take additional time to maintain index entries.

Consider deleting fewer rows at a time. If you delete more than 5,000 rows at once, the delete query may attempt to escalate to a table lock. If there is a lot of concurrent activity, the attempt to acquire a table lock may block while other requests complete.

For example, this loop deletes 4,000 rows maximum at a time:

declare @RowCount int = 1
while @RowCount > 0
begin
    delete top (4000)
    from [log].[HttpRequestLogEntries]
    where DateTimeUtc < dateadd(day, -3, getutcdate())

    select @RowCount = @@rowcount
end

Also, check for database triggers. If a trigger is firing when rows are deleted, it is possible code in the trigger is causing a long delay.

Delete old entries from table in MSSQL

You can use DATALENGTH to get the size of the data in a particular column. With a window function, you can sum up a running total of DATALENGTH values. Then you can delete all records in a table that push you past a desired max table size. Here's an example:

-- Sample table with a VARBINARY(MAX) column
CREATE TABLE tmp (id INT IDENTITY(1,1) PRIMARY KEY, col VARBINARY(MAX))

-- Insert sample data - 20 bytes per row.
;WITH cte AS 
(
    SELECT 1 AS rn, HASHBYTES('sha1', 'somerandomtext') t
    UNION all 
    SELECT rn + 1, HASHBYTES('sha1', 'somerandomtext')
    FROM cte
    WHERE rn< 5000  
)
INSERT INTO tmp (col)
SELECT t FROM cte
OPTION (maxrecursion 0)

-- @total_bytes is the desired size of the table after the delete statement
DECLARE @total_bytes int = 200

-- Use the SUM window function to get a running total of the DATALENGTH
-- of the VARBINARY field, and delete when the running total exceeds
-- the desired table size.
-- You can order the window function however you want to delete rows
-- in the correct sequence.
DELETE t
FROM tmp t
INNER JOIN
(
    SELECT id, SUM(DATALENGTH(col)) OVER (ORDER BY id) total_size
    FROM tmp
)sq ON t.id = sq.id AND sq.total_size > @total_bytes

Now check what's left in tmp: 10 rows, and the total size of the "col" column matches the 200 byte size specified in the @total_bytes variable.

UPDATE: Here's an example using your sample data:

CREATE TABLE tmp (id INT PRIMARY KEY, contact VARCHAR(100), country VARCHAR(25))
GO

INSERT INTO tmp VALUES 
(1, 'Maria Anders', 'Germany'),
(2, 'Francisco Chang', 'Mexico'),
(3, 'Roland Mendel', 'Austria'),
(4, 'Helen Bennett', 'UK'),
(5, 'Yoshi Tannamuri', 'Canada'),
(6, 'Giovanni Rovelli', 'Italy')
GO

-- @total_bytes is the desired size of the table after the delete statement
DECLARE @total_bytes INT = 40

-- Use the SUM window function to get a running total of the DATALENGTH
-- of the VARBINARY field, and delete when the running total exceeds
-- the desired table size.
-- You can order the window function however you want to delete rows
-- in the correct sequence.
DELETE t
FROM tmp t
INNER JOIN
(
    SELECT id, SUM(DATALENGTH(contact)) OVER (ORDER BY id)
        + SUM(DATALENGTH(country)) OVER (ORDER BY id) total_size
    FROM tmp
)sq ON t.id = sq.id AND sq.total_size > @total_bytes

SELECT * FROM tmp -- 2 rows left!

SQL to delete the oldest records in a table

When you use ORDER BY with ROWNUM the ROWNUM is applied first, so you don't get the results you expect. You could modify your SQL to:

delete from myTable where pk not in 
( SELECT pk FROM 
   ( SELECT pk FROM myTable  order by created DESC)
  where rownum <5
)

There are many other ways to write this. If the table is large and most rows will be deleted then maybe this will be faster:

delete from myTable where created < 
( SELECT MIN(created) FROM 
   ( SELECT created FROM myTable order by created DESC)
  where rownum <5
)

---

Related Topics