Update values in one table based on contents of another

Question

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?

Answer 1

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).

Answer 2

It sounds like you're still having some problems with the query Stefan provided. There are potential issues as you expand outside of his test case.

As written, his query does not handle:

• Tying the order_details rows to the right row from products,
• Multiple rows in order_details that should be used when adjusting the units_in_stock, and
• Cases where there's no order_details rows to update a particular row from products.

Here's a modified version of his query that handles these issues:

update products 
set  products.unit_in_stock = products.unit_in_stock - NVL( (
  select 
    SUM(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'
    and OD.product_id = products.product_id
) , 0)
;

And, here's the corresponding dbfiddle link

Notes:

• I covered the possibility of multiple rows from order_details for a given product_id by taking the SUM of OD.quantity.
• I covered the possibility that a product_id would not have any matches by checking if we got back a NULL value from the subquery, and substituting 0 for the NULL.
• I added a check to the subquery's WHERE clause to make sure that for any given row in products, we only look at order_details with the same product_id.

Important note: The above code does nothing to prevent rows from order_details from being used to update products.units_in_stock more than once. You should not apply this, as is, more than once.

To fix this, you need to do one of the following:

• Add an UPDATE trigger to orders that checks if the order has been changed so that it did not meet both conditions (marked as shipped, and has a shipping date) before, and it does now; if so, update products.units_in_stock from all related order_details records. If an order can be initially added as shipped, with a shipping date, then you'd need this same code in an INSERT trigger as well. (Note that even this is not sufficient to protect things, if this_order_was_shipped can change, or shipped_date can be set back to NULL. Ideally, if that happened, you'd need to reverse the process, restoring the quantity from the order_details record back to products.units_in_stock);
• Set permissions so that orders and order_details can only be inserted or modified via stored procedures, and manage the changes in the stored procedure (some people don't like triggers); or
• Add a flag of some sort to the orders or order_details table, to indicate that the items have already been adjusted out of products.units_in_stock, update the query to ignore rows with the flag, and set the flag when you run this query.

---

With respect to your follow-up questions:

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

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

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

The FROM clause establishes O as an alias for Orders and OD as an alias for Order_Details.

So, the FROM clause tells the engine that OD.Quantity is Order_Deteails.Quantity (not Orders.Quantity, by the way), and that O means Orders.

If you need to include the same table twice in a FROM clause, aliases are absolutely necessary. Imagine that your order had two fields for employee IDs - one for the person who took the order, and one for the person who took the cancellation of the order. If you needed both of those employee's names, you'd need to join that table to Orders twice - something like:

SELECT O.orderno
      ,Employees.emp_name as Took_Order
      ,cancel_emp.emp_name as Took_Cancel
  FROM Orders O
         INNER JOIN Employees ON (O.order_taken_by = Employees.emp_id)
         LEFT  JOIN Employees cancel_emp ON (O.cancel_taken_by = cancel_emp.emp_id)
;

Without a separate name for cancel_emp, how could the engine know which emp_name to put where?

If you left the aliases in place, but tried to use the original table names elsewhere, like this:

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

Then the problem is presumably due to the fact that, once an alias is in place, Oracle is going to expect you to just use the alias. If you tell it that the Orders table should be referenced as O, and then try to user Orders somewhere, it's going to think that's a different table, and not recognize it. If you don't want to use an alias, make sure you drop it in the FROM clause:

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

Then, everything should work as expected.

Answer 3

Looks like this should do it for Oracle:

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