Jump to content


Photo

getting around UPDATE subquery table restrictions?


  • Please log in to reply
4 replies to this topic

#1 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 11 October 2006 - 02:31 PM

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

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.


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.

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 11 October 2006 - 07:57 PM

Usually one would use temporary tables.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 11 October 2006 - 10:05 PM

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

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 11 October 2006 - 10:51 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 12 October 2006 - 04:33 PM

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.

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





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users