CODEWITHSUNDEEP
sqloracle-database
yogieputra
yogieputra

Reputation: 647

update table query from other table

I have the following problem:

In my database I have 1st table : item_price

Id_item   Dept   Price
1          7      1500
2          7      100
3          7      200
4          7      300
5          7      400
6          7       0
7          7       0
8          7       0
9          7       0
10         7       0

And I have 2nd table : Order_history

Order_no    Id_item   Dept    Price    Date_order
A1             1       2       700     01/05/2014
A2             2       3       800     02/21/2014
A3             3       7       200     03/25/2013
A4             3       7       300     04/15/2014
A5             4       7       300     05/05/2014
A6             5       7       400      06/15/2014
A7             6       7        120    07/16/2014
A8             7       7       1400     08/19/2014
A9             8       7       150      09/25/2014
A10            9       7       4500    10/31/2014
A11           10       7       8000    11/15/2014

All I want to do now is: I want to update field price in item_price WHERE Id_item in item_price = Id_item in Order History dept in item_price = dept in order_history AND Date_Order = 01/05/2014 - 11/07/2014

something like === 

update item_price 
set price = (select item_number from item_history )
where date_order = '01/01/2014' - '11/07/2014' and dept = '7';

Can you guys help me?

Upvotes: 2

Views: 66

Answers (2)

Augustus
Augustus

Reputation: 1

SQL

Test on Dev First and verify results! Please

UPDATE item 
SET item.Price = history.price 
FROM dbo.item_price item 
JOIN dbo.Order_History history ON item.Id_Item= history.Id_Item AND item.Dept = History.Dept 
WHERE history.Date_Order Between '01/05/2014' AND '11/07/2014'

Upvotes: 0

Donal
Donal

Reputation: 32713

Oracle does provide support for updates with joins. What you can do is something like this (note I have not tested this):

MERGE
INTO item_price 
USING   (
    SELECT  ip.Id_item AS rid, oh.price as hist_price
    FROM    item_price ip
    JOIN    Order_history oh
    ON      oh.id_item = ip.id_item
    WHERE   
    oh.date_order between TO_DATE('01/01/2014') and TO_DATE('11/07/2014')
)
ON (Id_item = rid)
WHEN MATCHED THEN
UPDATE
SET price = hist_price;

MySQL syntax would be something like this:

UPDATE item_price ip
    JOIN Order_history oh ON oh.id_item = ip.id_item  
    WHERE
    oh.date_order between 
        STR_TO_DATE('01/01/2014', '%m/%d/%Y') and 
        STR_TO_DATE('11/07/2014', '%m/%d/%Y') 
SET ip.price = oh.price;

Upvotes: 1

Related Questions