Delete all rows apart from last 100

[d_barszczak]

Hi all,

 

I am developing a chat application which stores messages in a database. I don't need to keep any more than 100 lines so i will need to run a query that deletes all rows apart from the last 100 submitted.

 

id = message id

room = room id

submit_time = Submitted time

msg = message

 

SELECT * FROM post_table WHERE `room` = '1' limit last 100

[dreamwest]

One way is to get the number of rows, then minus 100 then limit deletion submit_time ASC

 

Thatll leave 100 newest rows

 

 

[d_barszczak]

OK thanks, was hoping there would be a simple query

[Mchl]

Try:

DELETE FROM post_table WHERE id NOT IN(SELECT id FROM post_table ORDER BY submit_time DESC LIMIT 100)

[fenway]

LEFT JOIN ... IS NULL might be faster.