CODEWITHSUNDEEP
phpmysql
soooo
soooo

Reputation: 145

Mysql delete all except 100 new rows

Hello I need to empty all table except the x new rows i have already test

How to delete all rows from a table except newest 10 rows

SQL Delete all rows except for last 100

But not work, my mysql version doesn't support limit "IN" (actual version Ver 14.14 Distrib 5.5.46)

My table is like that

ID (int)
DATE (timestamp)

Who i can empty without loop ? Thanks

Upvotes: 1

Views: 1029

Answers (3)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can do it with a single SQL DELETE statement that uses variables:

DELETE mytable
FROM mytable 
INNER JOIN (
  SELECT ID, @rn := @rn + 1 AS rn
  FROM mytable
  CROSS JOIN (SELECT @rn := 0) AS var
  ORDER BY DATE DESC
) AS t ON mytable.ID = t.ID
AND t.rn > 100

The derived tasble t is used to assign each ID of mytable a row number. Numbering uses ORDER BY DATE DESC, hence 100 most recent records are going to have rn in [1-100]. DELETE removes any row of mytable except those having rn in [1-100].

Demo here (Demo keeps 10 more recent records instead of 100)

Upvotes: 2

Aminadav Glickshtein
Aminadav Glickshtein

Reputation: 24590

You can do it by using number of SQL statments one after another.

You can do it by get the first ID that you want to delete, and then delete all the rows newer that ID

// Create the memory table for storing the IDs
create table mem(
   first100 integer
);

// Insert to the memory table the list of 100 last ids
insert into mem(first100) select id from table order by id desc limit 100;

// Insert to Mysql variable @a, the id of the first id  to delete
set @a=select fist100 from mem order by id limit 1;

// Delete all the id's after the one you want to delete
delete from table where id>=@a

Update: I posted this answer befor you wroted that the error message is: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME

Anyway, I keep it, maybe this method will help to someone else .

Upvotes: 1

Sugumar Venkatesan
Sugumar Venkatesan

Reputation: 4028

Try this

<?php
$mysqli=new mysqli("localhost", "root", "", "db");
$result=$mysqli->query("select id from tabl order by id desc" );
$res=$result->fetch_array();
echo $res[0];
$id=$res[0]-100;
$mysqli->query("delete from table where id < $id");
?>

Upvotes: 3

Related Questions