Jump to content

Archived

This topic is now archived and is closed to further replies.

akitchin

getting around UPDATE subquery table restrictions?

Recommended Posts

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.

Share this post


Link to post
Share on other sites
Usually one would use temporary tables.

Share this post


Link to post
Share on other sites
in which case i guess an extra query is necessary regardless.  alright, thanks.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.