getting around UPDATE subquery table restrictions?

[akitchin]

after some sussing about in phpMyAdmin, i read this line in the MySQL manual that explains why my query didn't work:

[quote]Another restriction is that currently you cannot modify a table and select from the same table in a subquery. This applies to statements such as DELETE, INSERT, REPLACE, UPDATE, and (because subqueries can be used in the SET clause) LOAD DATA INFILE.[/quote]

now i see why that makes sense, simply because the value you are SELECTing might change during or after the global UPDATE query.  however, the update to take place depended on an aggregate (COUNT()) function from that same table.  i've gotten around this for now by running them in separate queries, but i like to try to condense my queries where possible.  anyone know of a way around this?  my guess is no, but i'm not a MySQL guru.

[fenway]

Usually one would use temporary tables.

[akitchin]

in which case i guess an extra query is necessary regardless.  alright, thanks.

[fenway]

I'm not sure whether or not this is an MySQL limitation, or whether this is part of the SQL-2003 standard.  If it's the former, they may have relaxed the restrictions in v5.x, but I haven't checked.

[shoz]

You should be able to JOIN on a derived table which would hold the result of the subquery that I assume you're currently using in the WHERE clause.

[code]
UPDATE
table1 AS t1
INNER JOIN
(
    SELECT
    id
    FROM
    table1
    GROUP BY
    id
    HAVING
    COUNT(*) > 2
) AS t2
ON
t1.id = t2.id
SET
t1.column = ...
[/code]