MySQL Forums
Forum List  »  Newbie

Re: subqueries
Posted by: Felix Geerinckx
Date: May 25, 2005 02:50PM

Michael Szklarzewski wrote:

> UPDATE POST SET CLASS=1 WHERE ID IN
> (SELECT ID FROM POST ORDER BY RAND() LIMIT=300);

> but I obtain a respond message:
> This version of MySQL database doesn't yet support 'LIMIT' & 'IN/ALL/ANY/SOME subquery'

One way: use a temporary table with multiple-table UPDATE:

USE test;
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
class INT
);

INSERT INTO foo (id, class) VALUES
(NULL, 0), (NULL, 1), (NULL, 0), (NULL, 1), (NULL, 2), (NULL,3),
(NULL, 0), (NULL, 1), (NULL, 0), (NULL, 1), (NULL, 2), (NULL,3),
(NULL, 0), (NULL, 1), (NULL, 0), (NULL, 1), (NULL, 2), (NULL,3),
(NULL, 0), (NULL, 1), (NULL, 0), (NULL, 1), (NULL, 2), (NULL,3);

DROP TABLE IF EXISTS temp_foo;
CREATE TEMPORARY TABLE temp_foo SELECT id FROM foo ORDER BY RAND() LIMIT 10;

UPDATE foo, temp_foo SET foo.class = 100 WHERE foo.id = temp_foo.id;

--
felix
Please use BBCode to format your messages in this forum.

Options: ReplyQuote


Subject
Written By
Posted
May 25, 2005 11:58AM
Re: subqueries
May 25, 2005 02:50PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.