Delete top rows from SQL Server Everywhere

I use a database table as a log. For simplicitys sake here's a scaled down version:
CREATE TABLE Log (
Date datetime NOT NULL PRIMARY KEY,
Title nvarchar(255) NOT NULL
)

Problem: I don't want the log to contain more than 11.000 items. When log goes over that, it should delete 1.000 rows from the top.

Question: How do I delete the first 1.000 rows (oldest entries) from this table in SQL Server Everywhere?

Limitations: SQL Server Everywhere does not suppport triggers, stored procedures, TOP n or SET ROWCOUNT.

Thanks.
halligangAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:
You will need the id column, so go ahead and add it.  You will also need a work table (temp table would be best if available) with an ident column, like so:

CREATE TABLE workTable (  --or #workTable
    ident INT IDENTITY(1,1),
    id INT  --from main table  
    )

Then:

IF (SELECT COUNT(datetime) FROM tableName >= 11000)
BEGIN --{
    TRUNCATE TABLE workTable  --or DELETE FROM
    INSERT INTO workTable (id)
    SELECT id
    FROM tableName
    ORDER BY datetime
    DELETE FROM tableName
    WHERE id IN (SELECT id FROM tableName WHERE ident <= 1000)
END --}
0
 
HuyBDCommented:
You can add the below query after insert new record or schedule in your application
delete from Log  where Date not in (select top 11000 Date from Log  order by Date desc)
0
 
adatheladCommented:
Hi

>> Limitations: SQL Server Everywhere does not suppport triggers, stored procedures, TOP n or SET ROWCOUNT
Makes it tricky :)

I'd suggest an alternative, whereby you delete rows that are over x days/weeks/months/years old.
This would allow you to use something like:
DELETE FROM Log WHERE Date < (DATEADD(m, -6, GETDATE()))
(This example deletes things over 6 months old).

You could then schedule this to run as a job every day.

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
halligangAuthor Commented:
HuyBD: thanks, but TOP is not a keyword in Everywhere edition

adathelad: thanks, but size is an issue, since some days may add a significant amout of log entries while others may produce none


A solution I've been thinking about, but didn't quite complete (since stored procedures is not an option):

Add an Identity column, Id

Then, at regular intervals make three calls to the database
SELECT MIN(Id) from Log
SELECT MAX(Id) from Log

In my program code:
int tableRows = max - min;
if (tableRows > 11000)
{
int idLimit = max - 10000;
(create query:
DELETE FROM Log WHERE Id < idLimit;
}

This may be a hint for a solution. Two problems with the above:
1. Id column will eventually wrap. How can this be handled? A solution would be to copy all contents to a new table which effectively reset the value, but will be somewhat inefficient.
2. Failed inserts will cause the identity column to skip over several values.

Thanks.
0
 
b2mje112Commented:
1.  select count(*) from Log
2.  Not sure how to do it if ID wraps.
0
 
Scott PletcherSenior DBACommented:
Almost forgot, you should add an index on (datetime, id) on tableName.
0
 
HuyBDCommented:
get something from ScottPletcher' idea

create table #tmp(ID int identity(1,1), date int,title varchar)
insert into #tmp(date,title) select * from Log
delete  from #tmp where id<1100
delete  from Log
insert into Log select date,title from #tmp
drop table #tmp

//you can check record count

create table #tmp(ID int identity(1,1), date int,title varchar)
insert into #tmp(date,title) select * from Log
if exists (select 1 from #tmp where id>1100)
 begin
   delete  from #tmp where id<1100
   delete  from Log
   insert into Log select date,title from #tmp
 end
drop table #tmp
0
 
Scott PletcherSenior DBACommented:
Thanks!  But, D'OH, I left one thing out:


IF (SELECT COUNT(datetime) FROM tableName >= 11000)
BEGIN --{
    TRUNCATE TABLE workTable  --or DELETE FROM
    INSERT INTO workTable (id)
    SELECT TOP 1000 id     --<<--
    FROM tableName
    ORDER BY datetime
    DELETE FROM tableName
    WHERE id IN (SELECT id FROM tableName WHERE ident <= 1000)
END --}
0
 
halligangAuthor Commented:
Thanks. I built on your solution. But the final comment didn't follow the rules... TOP is not allowed on SQL Server Everywhere... :-D
0
 
Scott PletcherSenior DBACommented:
OK; technically it's not needed -- it would just be more efficient to insert only the first 1000 instead of all of them.
0
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.