TalkPHP - MySQL doesn't support LIMIT subquery...

TalkPHP (http://www.talkphp.com/forums.php)

- MySQL & Databases (http://www.talkphp.com/mysql-databases/)

- - MySQL doesn't support LIMIT subquery... (http://www.talkphp.com/mysql-databases/5055-mysql-doesnt-support-limit-subquery.html)

MySQL doesn't support LIMIT subquery...

Without realizing that MySQL doesn't yet support the following syntax, I wrote the following query (simplified here with data already generated) to perform a delete;

Code:

DELETE FROM `wp_1_statpress` WHERE SUBSTR(timestamp, 1, 10) <> '2009-10-23' AND id NOT IN (SELECT id FROM `wp_1_statpress` WHERE SUBSTR(timestamp, 1, 10) = '2009-10-22' LIMIT 30)

Which returns error #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' *!*

So I attempted to rewrite the query using an inner join, but I'm getting an error in my syntax that I can't seem to deduce just yet;

Code:

DELETE FROM  `wp_1_statpress` AS physical INNER JOIN (SELECT id FROM `wp_1_statpress` WHERE SUBSTR(timestamp, 1, 10) = '2009-10-22' LIMIT 30) AS virtual ON physical.id = virtual.id WHERE SUBSTR(timestamp, 1, 10) <> '2009-10-23'

Can somebody spot my error? I'd like to perform this operation without having to pull the rows and re-insert them later if I can. That would be a plain pain in the arse. :-D

Temporary tables are Snazzy. I bypassed having to code any extra PHP to do this by using straight SQL;

Code:

CREATE TEMPORARY TABLE old_data SELECT * FROM `wp_1_statpress` WHERE SUBSTR(timestamp, 1, 10) = '2009-10-22' LIMIT 30

DELETE FROM `wp_1_statpress` WHERE SUBSTR(timestamp, 1, 10) <> '2009-10-23'

INSERT INTO `wp_1_statpress` SELECT * FROM `old_data`

This seems to be a pretty speedy way of getting what I wanted done, but nevertheless if I could perform it one query, I would prefer to do so. So while the problem is resolved for all intensive purposes, if anybody can see what's wrong with my original query I'd still be interested.