mysql delete all rows and keep latest x left

Question

Hi, i have a table like

entryid,  roomid
 1           1      
 2          55
 3           1
 4          12
 5           1
 6          44
 7           1
 8           3
 9           1

now I would like to delete ALL entries where roomid = 1 and keep the latest 3 from roomid = 1 left (best with just one command)

so finaly entryid: 1 & 3 came deleted and entryid 6, 7, 9 keeps staying (for sure all other roomid will still stay)

EDIT: Thanks for help. Bellow i Added my own Solution, for everyone interested

I started a new Question how to bring that into ONE command. You may help me there. Thanks

Answer 1

Something like

delete from TABLE 
where roomid=1 
   and entryid not in 
   (select entryid from TABLE where roomid=1 order by entryid desc limit 0, 3)

might work.

Answer 2

You can store the ids of the superfluous rooms in a temporary table, and delete based on that:

create temporary table tmpTable (id int);

insert  tmpTable
        (id)
select  id
from    YourTable yt
where   roomid = 1
        and 3 <=
        (
        select  count(*)
        from    YourTable yt2
        where   yt2.roomid = yt.roomid
                and yt2.id > yt.id
        );

delete  
from    YourTable
where   ID in (select id from tmpTable);    

This results in:

ID  roomid
2   55
4   12
5   44
6   1
7   1
8   3
9   1

Answer 3

DELETE supports an ORDER BY and LIMIT clause, so it is possible. However, due to DELETE's referential restrictions and parameters of LIMIT you need two queries.

SELECT COUNT(*) AS total FROM table  WHERE roomid = 1;
-- run only if count is > 3
DELETE FROM table WHERE roomid = 1 LIMIT total - 3;

Please note this will probably require an intermediary technology. I have shown the queries for reference.

Answer 4

T-SQL guy here, but can t-sql do:

SELECT
    *
FROM 
    TABLE A

    LEFT JOIN (SELECT TOP 3 entryID FROM TABLE WHERE roomID = 1 ORDER BY entryID DESC) B
    ON A.entryID = B.entryID 
WHERE       
    A.roomID = 1 AND
    B.entryID IS NULL

Then replace the select with DELETE TABLE FROM...

?

Answer 5

SET @deleting = (SELECT COUNT(*) FROM tbl WHERE roomid = 1) - 3;
-- run only if @deleting is > 0
PREPARE stmt FROM 'DELETE FROM tbl WHERE roomid = 1 ORDER BY entryid LIMIT ?';
EXECUTE stmt USING @deleting;

Answer 6

Thanks for all your Help.. I took them all together and use now this Solution :) For me this Step is closed. Thanks.

// Delete older comments from room 1 (keep last 3 left)
// Step 1:
$sql_com = "SELECT id FROM `mytable` WHERE roomid = '1'";
$result = mysql_query ($sql_com); $num_rows = mysql_num_rows($result);       

// Step 2:
if ($num_rows > 3) {
  $sql_com = "SELECT id FROM `mytable` WHERE roomid = '1' ORDER BY id DESC LIMIT 3,1";  
  $result = mysql_query ($sql_com);
  $row = mysql_fetch_array($result, MYSQL_NUM);
}

// Step 3:  
$sql_com = "DELETE FROM `mytable` WHERE roomid = '1' AND id < ".$row[0];
$result = mysql_query ($sql_com);