I ahve a problem with updating a row using a field from another row in the
same table.
The mySQL manual says:
Error 1093 (ER_UPDATE_TABLE_USED)
SQLSTATE = HY000
Message = "You can't specify target table 'x'
for update in FROM clause"
This error will occur in cases like this:
UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);
It's okay to use a subquery for assignment within an UPDATE statement,
since subqueries are legal in UPDATE and DELETE statements as well as
in SELECT statements. However, you cannot use the same table, in this
case table t1, for both the subquery's FROM clause and the update target.
So what is the best way to do something like
UPDATE accounts SET balance = balance + (SELECT balance FROM accounts WHERE
userID = 100) WHERE userID = 101
Russell.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
