CODEWITHSUNDEEP
mysql
PHPFan
PHPFan

Reputation: 796

Mysql 1093 - Table is specified twice

I have really searched and this question has been answered here

1093 Error in MySQL table is specified twice

but the answer doesn't help me

I have this accounts table

Accounts table

But I am facing error 1093 - Table is specified twice, when trying to update account balance Although I give the table two names t1 and t2

UPDATE accounts t1 
SET Account_Balance = Account_Balance+(
    SELECT SUM(Credit)-SUM(Debit) 
    FROM accounts  t2   
    WHERE Account_Id=1
)

Create accounts table statement

CREATE TABLE `accounts` (
 `Account_Id` int(11) NOT NULL AUTO_INCREMENT,
 `Account_Name` varchar(100) NOT NULL,
 `Account_Name_English` varchar(50) NOT NULL,
 `Account_Balance` decimal(15,2) NOT NULL DEFAULT '0.00',
 `Credit` decimal(15,2) NOT NULL DEFAULT '0.00',
 `Debit` decimal(15,2) NOT NULL DEFAULT '0.00',
 PRIMARY KEY (`Account_Id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8

Upvotes: 1

Views: 121

Answers (1)

Nick
Nick

Reputation: 147146

Using an alias doesn't solve the problem of not being able to specify a table that is being updated in a SELECT on the right hand side of an expression. One way to work around this issue is to use a multi-table UPDATE:

UPDATE accounts t1
CROSS JOIN (SELECT SUM(Credit)-SUM(Debit) AS `change`
            FROM accounts
            WHERE Account_Id=1) t2
SET t1.Account_Balance = t1.Account_Balance + t2.change

Note I'm not sure the location of the WHERE Account_Id = 1 is correct; this will update all Account_Balance fields in accounts to their old balance plus the change from Account_Id 1. If that is what you want, this is fine, otherwise you might need an additional WHERE clause on the UPDATE i.e.

UPDATE accounts t1
CROSS JOIN (SELECT SUM(Credit)-SUM(Debit) AS `change`
            FROM accounts
            WHERE Account_Id=1) t2
SET t1.Account_Balance = t1.Account_Balance + t2.change
WHERE Account_Id = 1

Or to update all accounts with their own change:

UPDATE accounts t1
JOIN (SELECT Account_Id, SUM(Credit)-SUM(Debit) AS `change`
      FROM accounts
      GROUP BY Account_Id) t2 ON t2.Account_Id = t1.Account_Id
SET t1.Account_Balance = t1.Account_Balance + t2.change

Here's a demo of all three queries in operation.

Upvotes: 2

Related Questions