CrazyEngineers
CrazyEngineers

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

    Gaurav Deshmukh

    Member

    Updated: Oct 27, 2024
    Views: 1.5K
    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
    0
    Replies
Howdy guest!
Dear guest, you must be logged-in to participate on CrazyEngineers. We would love to have you as a member of our community. Consider creating an account or login.
Replies

  • Ankita Katdare

    Administrator • Apr 16, 2015

    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?
    Are you sure? This action cannot be undone.
    Cancel

  • Manish Goyal

    Member • Apr 17, 2015

    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
    Are you sure? This action cannot be undone.
    Cancel
Home Channels Search Login Register