You can't specify target table for update in FROM clause [Error 1093]

Hi #-Link-Snipped-#
There should be a workaround for that.
Are you using any other query along with that in your code?
Also, did you try separating the query in parts?

Gaurav v. DeshmukhThere is table named as ,
- loan (loan_number, branch_name, amount);

i have to perform operation on this table is,
-Decrement the loan by 5% whose amount is less than average amount;

The query which i tried is,

update loan
set amount=amount*0.95 where amount < (select avg(amount) from loan);

But i am getting error as,
-ERROR 1093 (HY000): You can't specify target table 'loan' for update in FROM clause

Assuming you have primary key loan_number, try this query

UPDATE loan ln
INNER JOIN
(
SELECT avg(amount) average, loan_number
FROM loan
) lg ON ln.loan_number= lg.loan_number
SET od.amount= og.average