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

Question

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.

I believe TABLEA has its own primary key ID and the id that you mentioned above is actually just the foreign key, right? — sicKo, Oct 01 '15 at 06:52
Please do not vandalize your posts. By posting on the Stack Exchange network, you've granted a non-revocable right for SE to distribute that content (under the [CC BY-SA 3.0 license](https://creativecommons.org/licenses/by-sa/3.0/)). By SE policy, any vandalism will be reverted. If you would like to disassociate this post from your account, see [What is the proper route for a disassociation request?](https://meta.stackoverflow.com/q/323395) — NobodyNada, Aug 13 '17 at 20:42
Please do not deface the original post. If this question gets re-edited, it will be flagged for moderation where the question will be locked and even you won't be able to re-edit. I have rolled it back to a previous revision. — Funk Forty Niner, Aug 14 '17 at 22:12

score 1 · Accepted Answer · answered Oct 01 '15 at 06:53

1

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.

answered Oct 01 '15 at 06:53

Sebastian Brosch

42,106
15
72
87

This worked...........DELETE FROM `ywg_bp_messages_messages` WHERE thread_id = 521 AND id NOT IN ( SELECT id FROM ( SELECT * FROM `ywg_bp_messages_messages` WHERE thread_id = 521 ORDER BY date_sent desc limit 10 )x ) – shakespeare Oct 01 '15 at 08:38

score 0 · Answer 2 · answered Oct 01 '15 at 07:00

I'm not sure how the table in buddypress works but I guess TABLEA should have its on primary key id. If TABLEA does have its own primary key id, here's my solution.

DELETE FROM TABLEA WHERE id = x AND TABLEA_id NOT IN 
    (SELECT TABLEA_id FROM TABLEA WHERE id = x ORDER BY date_sent DESC LIMIT 10)

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

2 Answers2