How to convert an Update a query from MS Access to Oracle SQL Software [duplicate]

Question

I have an update query below written in Microsoft Access and I would like to convert that to Oracle SQL Developer 17.2.0.188.
There is also an image in the attachment showing the query expression.

The query said: "update the Products.UnitsInStock to UnitsInStock-Quantity if the ShippedDate is not null and This_Order_Was_Shipped = "YES"

UPDATE Products INNER JOIN (
           Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
       ) ON Products.ProductID = [Order Details].ProductID
SET    Products.UnitsInStock = [UnitsInStock] -[Quantity]
WHERE  (
           ((Orders.ShippedDate) IS NOT NULL)
           AND ((Orders.This_Order_Was_Shipped) = "YES")
       );

Answer 1

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;