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

{updated@Wed May 15, 2013} #MySQL #mysql_error()

[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)

howabout says:

May 13, 2012 at 10:50

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

Reply
Khayrattee Wasseem says:

May 13, 2012 at 12:22

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! 🙂

Reply
Mike says:

October 12, 2013 at 03:25

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.

Reply
suleman khan says:

December 26, 2013 at 10:41

Thanks for posting it is really helpful to me,

Reply
ClaireElaine says:

June 24, 2014 at 11:09

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

Reply
ayu says:

March 2, 2015 at 10:30

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?

Reply
1. Khayrattee Wasseem says:
  
  March 15, 2015 at 12:22
  
  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 _
  
  Reply
Judith says:

June 25, 2015 at 12:46

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?

Reply