Board index » delphi » Delete sql statement

Alan

Delphi Developer
Tue, 01 Feb 2005 13:19:59 GMT

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.
 
Bill Tod

Delphi Developer
Tue, 01 Feb 2005 21:52:37 GMT

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)
Alan

Delphi Developer
Fri, 04 Feb 2005 07:33:20 GMT

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)

Bill Hanna

Delphi Developer
Fri, 04 Feb 2005 12:13:37 GMT

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

1. Problem with DELETE SQL statement in TADOCommand

2. Q - How Pack DBase Files with sql-delete statement

3. SQL Statement deletes too much!

4. BDE 5.10 Update SQL statement delete the 2nd and 3rd memo fields on my table

5. how can do cascade delete in sql statement

6. As Requested: Good SQL book and Examples of SQL's LIKE statement

7. Please Help (This message Pertains to SQL and the Create Table SQL Statement)

8. Problem w/SQL Insert statement, SQL Server nvarchar datatype, and colons :

9. IS TADOQuery ok for insert/update/delete statements

10. delete statement?