Bug #23316 cannot update a table and select from the same table in a subquery

• Submitted: 16 Oct 2006 2:58
• Modified: 16 Oct 2006 10:56
• Reporter: Fauzan Badriawan
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0
• OS: Windows (Windows XP)
• CPU Architecture: Any
• Tags: ADO

[16 Oct 2006 2:58] Fauzan Badriawan

Description:
I uses Mysql ver 5.0
I write a query like this :
"UPDATE ACCOUNT_TMP SET ENDING_BALANCE=(SELECT SUM(ACCOUNT_TMP) FROM ACCOUNT_TMP T WHERE T.CODE LIKE ACCOUNT_TMP.CODE+'%');"

and I got this error :
"You can't specify target table 'coba' for update in FROM clause"

why we can't specify target and source from the same table in update query, whereas we can do that in other database server (like mssql, firebird, sqlite, etc). what is the reason?

How to repeat:
I don't understand what this means!

[16 Oct 2006 10:56] Valeriy Kravchuk

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ (read http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html in this case) and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php.

This is a well-known and documented limitation. I hope, queries like that will be allowed some day, at least, for InnoDB tables.