Delete table data when it reaches a certain number

Question

Delete table data when it reaches a certain number

Navigation

#1 by (0 votes)

0

Talk about it!

I have a table called tb_chat with the fields id, message, name, date, time.

How do I delete data from this table when I reach '6' records?

php mysql

asked by anonymous 08.01.2018 / 16:55

1 answer

Formatting default value BRL in javascript I can not use Hosting on Firebase

score 0 · Answer 1

Since you can not perform a delete using the same table in a subquery, you should do this in two steps:

First, you can select the first item in the tb_chat table based on the number of records. This can be done in several ways, eg:

SELECT MIN (message_id) FROM tb_chat HAVING COUNT (message_id) > 6

SELECT message_id FROM tb_chat HAVING COUNT (message_id) > 6 ORDER BY message_id ASC LIMIT 1

After that, just execute the delete with the ID returned from the query (if the query does not return any records, there are only 6 items in the table)