MySQL doesn't support LIMIT subquery...

delayedinsanity · 10-23-2009, 07:25 PM

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.

delayedinsanity · 10-23-2009, 07:49 PM

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.