Nested SELECT in UPDATE statement

This one surprised me a little, I didn't expect it to throw an error. The error that comes us is 'You cant specify target table jobcard in update in from clause.'

Does this mean I cannot use a NESTED SELECT in an UPDATE?

 

You can't update a table that is in the nested select. As far as I know it's because of the way that mysql processes queries. Since the table is in the select, it would be updating the table as you are selecting from it, which would change the results.

Here's a work around: http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/

 

from

http://dev.mysql.com/doc/refman/5.0/en/update.html