MySQL UPDATE with SELECT on the same table in the same query

Published January 29th, 2009

I’ve been having a week where there’s a ton of stuff to be done in a short time, yet everything seems to take much longer than it should and every task seems to present multiple unforseen obstacles. 

Yesterday, for example, I was working on something that had already taken several days of my time when I’d hoped to get it done in hours. The final straw came when I found myself facing what appeared to be an intractable problem: a process that needed to insert multiple rows in a table, and increment a certain column in each row to get a unique value. I couldn’t use an auto-increment value in this case (there was one already), and using a separate query caused concurrency problems (resulting in non-unique values). I should just point out that this is a system that I’ve inherited, not one that I developed myself.

I started out with a simple update, like this:

UPDATE my_table SET some_value = ( MAX( some_value ) + 1 ) WHERE id = 123;

…which in hindsight was never going to work but anyway, it gave me this error:

#1111 - Invalid use of group function

So I thought about it a bit and then struck on this:

 UPDATE my_table SET some_value =
(
(
SELECT MAX( some_value )
FROM my_table
) +1
)
WHERE id = 123

…which gave me this error:

#1093 - You can't specify target table 'my_table' for update in FROM clause

…damn. I was starting to bang my head on the desk at this point, but Google came to my rescue by pointing me at this thread, which suggested using a nested sub-select. The result was the following rather ugly query:

UPDATE my_table
SET
my_value =
(
(
SELECT selected_value
FROM
(
SELECT MAX( my_value ) AS selected_value
FROM my_table
)
AS sub_selected_value
)
+ 1
)
WHERE id = 123

…which works. It’s not great  from a performance point of view since it uses a temporary table, but it does what it needs to. I hope in future that I will be given time to go back and rewrite this part of the system so as to entirely remove the issue.

Get a Trackback link

8 Comments

  1. Vighnesh on May 29, 2009

    Thanks this helped me. I needed same sort of solution.

  2. Bruno Ng on June 16, 2009

    Here’s the simplified version :
    UPDATE my_table t, (SELECT MAX(my_value) + 1 AS selected_value FROM my_table) AS t2 SET t.my_value = t2.selected_value WHERE t.id = 123;

  3. Rich on May 25, 2010

    Thanks! I’ve been struggling with this too. Thanks to Bruno too!

  4. did not work on July 9, 2010

    This didn’t work for me. I still get “You can specify target table ‘foo’ for update in FROM clause.”

  5. gopi on September 14, 2010

    Guys, I too have been breaking my head on this err… thanks for Bruno’s hint. I followed his ‘aliasing’ approach to build a complex query to update moving averages of quantity.
    Thanks Bruno !

    I had a simple table t, with cols ‘dt’ for date and ‘qty’ for quantity. Here is how i built step by step:

    step 1: //start with outline t5 is some random alias

    update t tmp () set tmp.mavg = t5.mavg where tmp.dt=t5.date;

    step 2: //added t5 alias definition, which shud have mavg and date members.

    update t tmp ( ()as t5) set tmp.mavg = t5.mavg where tmp.dt=t5.date;

    step 3: mavg and date aliases defined. new aliases t1 & t2 are create to calculate moving average.

    update t tmp ( ( select t1.dt as date, avg(t2.qty) as mavg …)as t5) set tmp.mavg = t5.mavg where tmp.dt=t5.date;

    final touch:

    update t tmp, ((select t1.dt as date, avg(t2.qty) as mavg from t t1, t t2
    where t1.dt >=’2007-01-04′ AND t1.dt BETWEEN t2.dt AND t2.dt+4
    group by t1.dt order by t1.dt
    ) as t5) set tmp.mavg = t5.mavg where tmp.dt=t5.date;

    yuppie…it worked !

    hope this approach will solve someones headache too !

  6. zag on October 7, 2010

    I had the same problem and I solved it by used variables:

    select @a:= select MAX( some_value ) from my_table;
    UPDATE my_table SET some_value = ( @a + 1 ) WHERE id = 123;

  7. I’ve had the same problem:
    #1093 – You can’t specify target table ‘my_table’ for update in FROM clause

    So, my first solution has been with variables (like zag):
    SELECT @next_value := MAX(some_value) + 1 FROM my_table;
    UPDATE my_table SET some_value = @next_value WHERE id = 123;

    Now I’m using your solution with Bruno’s simplified approach.

    Thanks everybody.

  8. WebDev on May 14, 2011

    Thanks a lot! UPDATE queries are pretty obscure and poorly documented.

Leave a comment

Comment Policy: First time comments are moderated. Please be patient.

OpenID

Anonymous