如何使用另一个表中的数据更新表

Question

im using php as my language, and oracle xe for my database.

i have table Order_details that store item_id, quantity.The table currently only hold data for item_id, while the quantity has null value.Lets say the item id is 038

i created another table order_details2 as a temp table that have the same row which is item_id,quantity. This table contains data for both item_id, and quantity which is 038, and 3

the problem is, i dont know how to use data in order_details2.quantity which is 3 to insert it into order_details.quantity using the same references item_id

can any1 please show me, how am i doing this? im very new to programming..

Answer 1

If I understand correctly what you're after, then something like this should work:

UPDATE
    (SELECT o1.item_id as id1, o1.quantity as qty1, o2.item_id as id2, o2.quantity as qty2
       FROM order_details o1
       JOIN order_details2 o2 on o1.item_id = o2.item_id) t
SET t.qty1 = t.qty2

You have to be careful about duplicate item_id values in any of the tables though.