DELETE query not working

[EchoFool]

Hey,

 

I have a query which is meant to delete all rows which are less than the last 31 ids with this WHERE clause. As i only display the most latest 31 rows the rows that are any older might aswell be deleted but it won't work for me.

 

The error i get is:

You can't specify target table 'messages' for update in FROM clause

 

DELETE FROM messages 
WHERE id <  (SELECT MAX(id) FROM messages) - 31 

 

Hope you can help!

[jskywalker]

do it in 2 steps

 

$max = mysql_query('SELECT MAX(id) FROM messages');
$max = $max -31;

mysql_query('DELETE FROM messages WHERE id < '.$max);

[EchoFool]

Thought that might be the way to do - i was hopeing a single query could. But i will use your suggestion.

 

Thanks

[Mchl]

DELETE FROM messages 
WHERE id NOT IN (SELECT id FROM messages ORDER BY id DESC LIMIT 31) 

[EchoFool]

Hi Mchl

 

Thanks for that - i tried it but i get:

 

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

 

Never had that before - v.odd error =/ any ideas why ?

[Mchl]

Oh well... MySQL does not support may features...

 

This should work

 

DELETE messages 
FROM messages 
LEFT JOIN (
  SELECT id FROM messages ORDER BY id DESC LIMIT 31
) AS sq 
USING (id) 
WHERE sq.id IS NULL