DELETE from table syntax

Question

I have a table with (amongst others) 2 columns (sayA and B) with a primary key and one with a timestamp (say C). I would like to delete everything but the last XXX entries (when sorted by the timestamp). Could anyone help me out with the syntax? I guess it should be along the lines of:

DELETE FROM Table WHERE A, B NOT IN (SELECT A, B FROM Table ORDER BY C DESC LIMIT XXX)

Is that correct SQL?

Edit: Somehow this question got closed being a duplicate. The referred question however only has answers that apply to a table which has exactly one primary key (or index) .. how do I expand this to a table where there are two?

As a general rule, when running a delete, use a select statement to get the where clause to where it needs to be and works, and replace the `select * from` part to `delete from` — gabe3886, Aug 15 '16 at 09:27
I don't understand .. the LIMIT 10 is included in a 'NOT IN' .. why would it delete those? — Lieuwe, Aug 15 '16 at 09:37
I don't know why this question has been downvoted. Seems perfectly reasonable to me. — eggyal, Aug 15 '16 at 09:46

score 0 · Answer 1 · answered Aug 15 '16 at 09:48

0

You can try this

DELETE FROM TABLE T1 INNER JOIN (SELECT A, B FROM TABLE ORDER BY C DESC LIMIT XXXX) AS T2 ON T1.A = T2.A AND T1.B = T2.B

in interior select you define conditions for delete.

answered Aug 15 '16 at 09:48

Jasmin Kurtic

141
12

But that deletes XXXXX entries doesn't it? I want to delete everything but those XXXXX entries. – Lieuwe Aug 15 '16 at 10:13
maybe you can DELETE FROM TABLE T1 left JOIN (SELECT A, B FROM TABLE ORDER BY C DESC LIMIT XXXX) AS T2 ON T1.A = T2.A AND T1.B = T2.B where T2.A is null AND T2.B is null now you are delete where row is not join – Jasmin Kurtic Aug 15 '16 at 10:40

DELETE from table syntax

1 Answers1