Specifying Target Tables in MySQL
In MySQL you can't use the same update table name within a sub-query in a where condition.The trick to get around this is to use another sub-query in the where clause!
Join the DZone community and get the full member experience.Join For Free
Have you tried updating something a simple as the following?
update category_tmp set last_update=NOW() where category_id in ( select category_id from category_tmp where name like 'A%' );
In MySQL you will get an error like this:
Error Code: 1093. You can't specify target table 'category_tmp' for update in FROM clause.
So it says that you can't use the same update
table name within the sub-query in the
where condition. The trick to get around this is to use another sub query in the where clause so it won't see the
table name being used! Here is a workaround:
update category_tmp set last_update=NOW() where category_id in ( select category_id from ( select category_id from category_tmp where name like 'A%' ) ID_LIST );
Notice that in MySQL, you must name your sub query result such as
ID_LIST, in order for it to re-select it again on the outer query! Otherwise it will throw this error:
Error Code: 1248. Every derived table must have its own alias
Published at DZone with permission of Zemian Deng, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
Implementing a Serverless DevOps Pipeline With AWS Lambda and CodePipeline
Automating the Migration From JS to TS for the ZK Framework
How To Integrate Microsoft Team With Cypress Cloud
Top 10 Engineering KPIs Technical Leaders Should Know