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

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

lol.

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

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.

Thanks for posting it is really helpful to me,

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

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 _

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?