Right. So ORACLE (at least 10g) does not accept JOIN criteria in an Update Statement. At least not in the way it's written in your post.
Instead, you'll need to write it as a simple SELECT statement (subquery), then WRAP that in an update, OR as an EXISTS.
Here's how to do it in a "subquery".
https://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle
I may have missed something, I didn't test this out fully in Toad as I have to work on some tasks. Anyone please feel free to edit.
*Note: ORACLE does not accept Brackets [], please remove your spacing in the columns when you convert to ORACLE if you're planning to. Underscores are easier. The workaround I'm using is Quotes.
Another note, I'm not sure I like the efficiency of the queries. Seems like it would be more efficient to start at Orders or Order Details vs. Products, especially considering all your WHERE clauses are on the Orders Table.
So your Select validation query would be something like:
SELECT Products.UnitsInStock AS OLDUnitsInStock,
(Products.UnitsInStock - Products.UnitsInStock - Quantity) AS NEWUnitsInStock
--FULLY QUALIFY THE QUANTITY COLUMN ABOVE TO AVOID AMBIGUITY
FROM Products
INNER JOIN "Order Details"
ON Products.ProductID = "Order Details".ProductID
INNER JOIN Orders
ON Orders.OrderID = "Order Details".OrderID
WHERE Orders.ShippedDate IS NOT NULL
AND Orders.This_Order_Was_Shipped = 'YES'
Your Update would look something like:
UPDATE
(
SELECT Products.UnitsInStock AS OLDUnitsInStock,
(Products.UnitsInStock - Products.UnitsInStock - Quantity) AS NEWUnitsInStock
--FULLY QUALIFY THE QUANTITY COLUMN ABOVE TO AVOID AMBIGUITY
FROM Products
INNER JOIN "Order Details"
ON Products.ProductID = "Order Details".ProductID
INNER JOIN Orders
ON Orders.OrderID = "Order Details".OrderID
WHERE Orders.ShippedDate IS NOT NULL
AND Orders.This_Order_Was_Shipped = 'YES'
) updateTable
SET updateTable.OLDUnitsInStock = updateTable.NEWUnitsInStock;