SQL Update with COUNT(*) less than 2

I have two tables to query from. An “Order_Details” table and a “Product” table. I need to COUNT(*) the amount of times each product has been ordered(identified by unique a “ORDER_ID”), from the “Order_Details” table. If the amount of times a product has been ordered is less than 2, I need to lower the price by 1 dollar. This is also done from two tables because items that haven’t been ordered, also need to have the price lowered. However, those Product_IDs will not show on the “Order_Details” table. I was able to figure out how to select all the products ordered less than 2 times. However, I don’t know how to do the same thing, but with updating the price. Here is the SELECT statement I figured out.

SELECT PRODUCT_NAME, COUNT(*)
FROM PRODUCT FULL JOIN ORDER_DETAILS
ON PRODUCT.PRODUCT_ID = ORDER_DETAILS.PRODUCT_ID
HAVING COUNT(*) < 2
GROUP BY PRODUCT_NAME;

Answer

Here is one way, using correlated subqueries to check if a given product has only one or zero orders. If so, then we decrement the price by one.

UPDATE PRODUCT p
SET PRICE = PRICE - 1
WHERE
    (SELECT COALESCE(MIN(ORDER_ID), 0) FROM ORDER_DETAILS od
     WHERE od.PRODUCT_ID = p.PRODUCT_ID) =
    (SELECT COALESCE(MAX(ORDER_ID), 0) FROM ORDER_DETAILS od
     WHERE od.PRODUCT_ID = p.PRODUCT_ID);