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 dodid not put any examples inon 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).