Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Specifying Target Tables in MySQL

DZone's Guide to

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!

· Database Zone ·
Free Resource

Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

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


Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

Topics:
mysql ,table

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}