-3

I have two columns price and quantity in two different tables product and orders. I want to set the value of a column in orders as price*quantity. I tried the following query but no luck

update orders_
set amount = (select price*quantity
              from product,
                   orders_
              where product.productid = orders_.productid);

productid is the common link between the two tables I keep getting the following error:

ERROR 1093 (HY000): You can't specify target table 'orders_' for update in FROM clause

jarlh
  • 42,561
  • 8
  • 45
  • 63
Deeptanshu Prasad
  • 1
  • Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS/product & DDL, which includes constraints & indexes & tabular-formatted base table initialization. PS You have a syntax error. Read the grammar & manual. – philipxy Sep 23 '19 at 13:03
  • Possible duplicate of [MySQL Error 1093 - Can't specify target table for update in FROM clause](https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause) – philipxy Sep 23 '19 at 13:06
  • This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. PS Please before you post look at the formatted version of your post below the edit box. Read the edit help re inline & block formats for code & quotations. – philipxy Sep 23 '19 at 13:12
  • You shouldn't store values computed from other columns like that, data inconsistency you know. Create a view instead, will always have up to date values. (Or at least use triggers.) – jarlh Sep 23 '19 at 13:24

3 Answers3

0

You can try below - with JOIN

UPDATE orders_
        INNER JOIN
    product ON product.productid=orders_.productid 
set amount=pricequantity
Fahmi
  • 37,315
  • 5
  • 22
  • 31
0

You can use just join update query.

Update orders o, product p
SET amount = pricequantity
WHERE p.productid=o.productid;
Chandan Rajput
  • 441
  • 5
  • 18
0

I believe you want:

update orders_ o join
       product p
       using (productid)
    set amount = (p.price * o.quantity);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786