Board index » delphi » Delete sql statement

Delete sql statement

How do I delete records from a table and keeps only the last n records.
eg. I just want to keep the last 100 records of the table.
 

Re:Delete sql statement


Define "last". Last based on what field in the records? What database
are you using?

On Fri, 16 Aug 2002 15:19:59 +1000, "Alan" <alanpl...@yahoo.com.au>
wrote:

Quote
>How do I delete records from a table and keeps only the last n records.
>eg. I just want to keep the last 100 records of the table.

--
Bill (TeamB)
(TeamB cannot respond to questions received via email)

Re:Delete sql statement


I am using the MySQL, just want to know is there a general way to do that ?
My table has fields:
UserID
TimeStamp
Action

Primary key will be TimeStamp.

Quote
"Bill Todd" <b...@notthis.dbginc.com> wrote in message

news:tr0qlug6fsc1gau6h6i47knk02td2loh7f@4ax.com...
Quote
> Define "last". Last based on what field in the records? What database
> are you using?

> On Fri, 16 Aug 2002 15:19:59 +1000, "Alan" <alanpl...@yahoo.com.au>
> wrote:

> >How do I delete records from a table and keeps only the last n records.
> >eg. I just want to keep the last 100 records of the table.

> --
> Bill (TeamB)
> (TeamB cannot respond to questions received via email)

Re:Delete sql statement


You can try something like

delete from tableName
where keyField not in
(select top 100 keyField  from tableName
order by keyField desc)

It works with mssql7 but not a bde local sql query, as Bill Todd said it
depends on the flavor of sql you are using.

Bill Hannan

Quote
"Alan" <alanpl...@yahoo.com.au> wrote in message news:3d5c8b05@dnews...
> How do I delete records from a table and keeps only the last n records.
> eg. I just want to keep the last 100 records of the table.

Other Threads