MySQL + UPDATE SELECT Statement + #1093 – You can’t specify target table for update in FROM clause

[contentblock id=300squaretextlink] [contentblock id=300squareimagelink]

What I Was Trying To Do

I needed to do an Update Select using MySQL, I was expecting it to be something like:

UPDATE MyTable
SET HitCount = ( SELECT sp.HitCount+1 FROM MyTable sp WHERE sp.email = ‘test@email.com’ )
WHERE email = ‘test@email.com’

Error Message I Got

Unfortunately, that returned the error message: #1093 – You can’t specify target table ‘MyTable’ for update in FROM clause. And the MySQL manual was helpless too saying at the bottom of the UPDATE documentation: “Currently, you cannot update a table and select from the same table in a subquery.

3 Solutions Found!

After spending some time googling here and there coupled with ‘trial-and-error’, I have found 3 Possible solutions to this issue as follows:

SOLUTION 1 – simplified version

UPDATE MyTable p1, ( SELECT (sp.HitCount+1) AS newcount FROM MyTable sp WHERE sp.email = ‘test@email.com’ ) AS p2
SET p1.HitCount = p2.newcount
WHERE email = ‘test@email.com’

SOLUTION 2 – using sub-query

UPDATE MyTable
SET HitCount = ( SELECT newcount FROM (SELECT (sp.HitCount+1) AS newcount FROM MyTable sp) AS result WHERE email = ‘test@email.com’ )
WHERE email = ‘test@email.com’

SOLUTION 3 – using variable

SELECT @currentcount := sp.HitCount FROM MyTable sp WHERE sp.email = ‘test@email.com’;
UPDATE MyTable SET HitCount = (@currentcount+1) WHERE email = ‘test@email.com’

Do You Know Which One Is The Way To Go & Why?

I do not really know the reasoning/logic behind and which is more correct to use, but I going for SOLUTION 1. If you know the ‘whys’ behind each of them and which one is the best to use, please just use the comment form below to let me and any readers (with same issue) to know about it, thanks!


8 Comment(s)

  1. Hi “howabout”,
    YES, this is a VALID query and it works fine.
    In my case I wanted to play-around with an UPDATE-SELECT statement..

    Thanks a lot for your comment! 🙂

  2. Hi,

    Thank you for posting these solutions. I had a similar problem in mysql this evening and I used your Solution #1 and it worked great for me.

  3. Thank you so much. I spent about three hours trying to find a solution to my update problem, and your Solution 1 worked beautifully. You’re a star.

    Yours,
    ClaireElaine

  4. Help me. I want to update my table.
    My query is.
    Update piutang set sisapiutang = (select totalbiaya, dibayarkan, (totalbiaya-dibayarkan) where id=1

    And error.
    Operand should contain 1 colum (s)
    What should i do?

    1. You have a wrong format there and also the absence of a closing )
      Update piutang set sisapiutang = (select totalbiaya, dibayarkan where id=1) where another condition here

      And what’s with this: totalbiaya-dibayarkan ? A column name is not separated by -, use an underscore _

  5. I have a problem with you solution

    UPDATE matricula
    SET
    matricula.total_Creditos = (SELECT suma FROM (select sum(cursos.creditos)as suma from cursos,matricula,det_matricula, estudiantes
    where cursos.codigoCurso= det_matricula.cod_Curso and
    matricula.num_Matricula= det_matricula.numMatricula and num_Matricula =2
    and estudiantes.carnet=matricula.carnet))
    WHERE matricula.num_Matricula =2 ;

    do not work Error Code: 1248. Every derived table must have its own alias what can i do?



Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.