Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.3.12, 5.5.37, 10.0.10
-
None
Description
The following update query gives error 1093 in MariaDB 10.0.10 but the same query works fine with MySQL 5.1.53-community version.
Query:
update accounts.accounts set balance=(select -1*sum(balance) from (SELECT balance FROM accounts.accounts where accountId like 'dealer%') AS copied) where accountId = 'OPERATOR'; |
Result:
ERROR 1093 (HY000): Table 'accounts' is specified twice, both as a target for 'UPDATE' and as a separate source for data |
Sample table:
select * from accounts.accounts;
|
+---------------+----------------+-------------+----------+---------------+-------------+---------+--------+----------+---------------------+---------------------+-------------+
|
| accountTypeId | accountId | accountName | currency | balance | creditLimit | version | status | password | createDate | expiryDate | extraFields |
|
+---------------+----------------+-------------+----------+---------------+-------------+---------+--------+----------+---------------------+---------------------+-------------+
|
| RESELLER | dealer-1 | | CFA | 199354.00000 | 0.00000 | 8 | Active | NULL | 2014-04-15 04:56:13 | 2014-04-15 04:56:13 | NULL |
|
| RESELLER | dealer-2 | | CFA | 0.00000 | 0.00000 | 0 | Active | NULL | 2014-04-15 04:56:15 | 2014-04-15 04:56:15 | NULL |
|
| RESELLER | dealer-3 | | CFA | 0.00000 | 0.00000 | 0 | Active | NULL | 2014-04-15 04:56:16 | 2014-04-15 04:56:16 | NULL |
|
| RESELLER | dealer-5 | dealer-5 | CFA | 0.00000 | 0.00000 | 0 | Active | NULL | 2014-04-16 04:41:50 | 2014-04-16 04:41:50 | NULL |
|
| RESELLER | FINANCE | | CFA | -200000.00000 | NULL | 2 | Active | NULL | 2014-04-15 04:56:13 | 2014-04-15 04:56:13 | NULL |
|
| RESELLER | OPERATOR | | CFA | 0.00000 | NULL | 0 | Active | NULL | 2014-04-15 04:56:13 | 2014-04-15 04:56:13 | NULL |
|
+---------------+----------------+-------------+----------+---------------+-------------+---------+--------+----------+---------------------+---------------------+-------------+
|
It appears that same table inside a sub-query is not supported in MariaDB version 10.0.10