Showing posts with label Update With SubQuery. Show all posts
Showing posts with label Update With SubQuery. Show all posts

Thursday, June 15, 2017

MySQL UPDATE with SUBQUERY of same table

I want to update a table named "test" with some sub-query where sub-query is the same "test" table. I wrote the below SQL first to update my "test" table:

UPDATE test SET roll=CONCAT(roll,"-",id) WHERE id IN (SELECT id FROM test WHERE id%2=0)

And got below error:

#1093 - Table 'test' is specified twice, both as a target for 'UPDATE' and as a separate source for data.

And in this case we have a nice solution provided by MySQL itself is processing temporary table, query is below:

UPDATE test SET roll=CONCAT(roll,"-",id) WHERE id IN (SELECT t.id FROM (SELECT * FROM test) AS t WHERE t.id%2=0)

And it works. Marked yellow color section actually do the temporary table works.