Update values in one table based on contents of another

join;oracleupdate

Software Name: Oracle SQL Developer Version 17.2.0.188

I have three tables: orders, order_details, and products.

I wanted to run a query to update units_in_stock in the products table based on quantity in order_details, where the associated orders row has been marked as shipped, and has a shipping date.

Here's what I tried:

UPDATE Products SET (Products.Unit_In_Stock = Products.Unit_In_Stock - Order_Details.Quantity)
INNER JOIN (
    Orders INNER JOIN Order_Details ON Orders.Order_ID = Order_Details.Order_ID
    ) ON Products.Product_ID = Order_Details.Product_ID
WHERE (
        ((Orders.Shipped_Date) IS NOT NULL)
        AND ((Orders.This_Order_Was_Shipped) = "YES")
        );

When I run this, I get:

Error at Command Line : 1 Column : 45

Line 1, Column 45 is the equal sign in the very first statement that said

Products.Unit_In_Stock = Products.Unit_In_Stock - Order_Details.Quantity

Can you help me get this working?

Best Answer

Assuming that your situation looks similar to this:

-- tables for testing
create table products
as
select 
  1 as product_id
, 100 as unit_in_stock
from dual ; 

create table orders
as
select 
  100 as order_id 
, 'YES' as this_order_was_shipped
, to_date('2017-09-14', 'YYYY-MM-DD') as shipped_date 
from dual;

create table order_details
as
select
  100 as order_id
, 1 as product_id
, 25 as quantity
from dual;

The tables now contain the following rows:

select * from products;

PRODUCT_ID  UNIT_IN_STOCK
1           100

select * from orders;

ORDER_ID  THIS_ORDER_WAS_SHIPPED  SHIPPED_DATE
100       YES                     14-SEP-17

select * from order_details;

ORDER_ID  PRODUCT_ID  QUANTITY
100       1           25

The update could look something like ...

update products 
set  products.unit_in_stock = (
  select 
    products.unit_in_stock - OD.Quantity
  from orders O
    join order_details OD on O.order_id = OD.order_id
  where O.shipped_date is not null
    and O.this_order_was_shipped = 'YES'
) 
where product_id = 1;

After executing the UPDATE, the PRODUCTS table contains:

select * from products;
PRODUCT_ID  UNIT_IN_STOCK
1           75

See dbfiddle here.

ADDITIONAL questions and answers:

1) The code that you wrote have OD.Quantity instead of Orders.Quantity. How does the program know that OD.Quantity is Orders.Quantity ?

In my example, the "quantity" of an ordered product is stored in the OrderDetails table. (The ORDERS table does not contain a quantity).

2) You use O instead of Orders. How does the program know that O is Orders ?

Table aliases. When you write something like SELECT ... FROM mytable M ... , then the letter M can be used as a "short name" / alias for the table. Usually, this makes queries easier to code (and read).

3) When I replace your code using Orders, and Order_Details instead of O and OD I got an error. Why is that ?

I don't know - as you did not put any examples on dbfiddle (or into the question itself). However, if you replace all Os and ODs with the full table names, it should work. See dbfiddle here. Notice that there is a WHERE clause for the UPDATE, too (this is needed, otherwise all rows in the PRODUCTS table would be updated).

Related Question