Search code examples
mysqlwordpressselectionbuddypressexcept

Mysql: Delete from table where ID, except rows in a selection?

The problem

I'm using buddypress for Wordpress it has a table for private messages in side which are thread ids for message threads. Currently there's no limit on how many messages can be in a thread.

I want to create a command that deletes all but the most recent 10 messages in a thread. Below is the logic, but I'm not sure the correct syntax to do it?

Any of you mysql geniuses know the answer?

DELETE FROM TABLEA WHERE id = X delete everything with thread ID x

EXCEPT 
(SELECT * FROM TABLEA WHERE id = X ORDER BY date_sent DESC LIMIT 10)

Selects most recent 10 I do not wish deleted.

Solution

  • This should working:

    DELETE FROM TABLEA WHERE id = X AND id NOT IN (
    SELECT TOP 10 id FROM TABLEA ORDER BY date_sent DESC
)

    The sub-select of this query get the last 10 sent items. The main query have to delete the item with id X except the item is on the result of the sub-select.