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

There 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

Replies

  • Ankita Katdare
    Ankita Katdare
    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?
  • Manish Goyal
    Manish Goyal
    Gaurav v. Deshmukh
    There 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

You are reading an archived discussion.

Related Posts

In inter networking, what is the difference between data rate and packet rate? How and to what extent can one affect the other?
Say if I were to study Blue tooth programming using Java this year, how can this help me if I decide to do for Doctorate program the next year? Any...
Now a days I am playing NES and Gameboy games in my machine using emulators. It's quite hard to find the ROMs of games without using the mighty TPB. So,...
If your about-to-go-dead camera's batteries need to be charged just before a long trip, who won't be annoyed? If the camera did not need batteries, wouldn't that really be something?...
Team of engineers from NASA and University of California, Berkeley have teamed up to develop unique folding robots that can survive lengthy drop on any planet and then began rolling...