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