Jump to content

getting around UPDATE subquery table restrictions?


akitchin

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.
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]

Archived

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

×
×
  • Create New...

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.