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

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!

If You Appreciate What I Do Here On Seven PHP :: 7PHP, You Could Consider:

  1. Following me on Twitter | @7php
  2. LIKE-ing my FaceBook page
  3. Subscribe to my Email List - see top-right subscription box
  4. Help diffuse this interview to the PHP ecosystem - Share & Spread the word as far as you can ==> That would be a FREE way to thank me
  5. Use my DigitalOcean referral link if you plan to use it as your VPS (it starts at only $5/month btw) or if you can refer it to your friends, highly appreciated.
  6. Support via Paypal donate - my Paypal ID is w@7php.com

{I'm thankful to your response(s)!}

Valuable Feedback / Comment / Review From People Like You

  1. howabout says:

    How about: UPDATE table SET counter=counter+1 WHERE email = ‘a@a.com’?
    lol.

  2. 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! :)

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

  4. suleman khan says:

    Thanks for posting it is really helpful to me,

  5. ClaireElaine says:

    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

  6. 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?

    • 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 _

  7. Judith says:

    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?

Speak Your Mind

*