Tuesday, April 19, 2011

Work-around for "You can't specify target table 'XYZ' for update in FROM clause"

MySQL is awesome. I *love* sql in general. I know many of you just cringed - to each his/her own. However, I really like all of the unique functionality MySQL provides. And id many cases you can utilize it's tools to overcome it's own shortcomings.

Quite some time ago I discovered a valuable work-around for situations when you want to update a table using a subquery in the where statement. I never posted it. Today I needed to do this again and I've never seen this on the net, so I felt it was an over-due posting.

Lets assume we have a table 'XYZ' with a date field and we want to update a few records that have date A and update them to date B.

update XYZ
set datefield = '2011-04-19'
where id IN (select id from XYZ where datefield = '2011-04-18')

MySQL will yell at us because this is not allowed: "You can't specify target table 'XYZ' for update in FROM clause"

Are you familiar with 'Derived Queries'? They are my best friend. And yours now too.

Check this out:

update XYZ
set datefield = '2011-04-19'
where id IN (select id from (select id from XYZ where datefield = '2011-04-18') d)

No yelling, IT WORKED!

Why?

Derived queries are basically temporary tables populated on the fly, used, and then dropped without any additional work from you. So, the query above, by using a derived query, and therefore a temporary table, is no longer querying itself, so it performs the update.

You can use derived queries outside of update statements to do all sorts of things that might otherwise be difficult in normal unions and joins. They are powerful and fast.

Notice the "d" following the derived query - that's an alias. If you don't give your derived query an alias (basically, naming your temporary table) MySQL will yell at you saying "Every derived table must have its own alias".


Neat-o new best friend, eh?

Viewer, meet Derived - Derived, meet Viewer. Please, get to know each other, have a good time. Best of luck!

No comments: