nzkiwi80
You don't record the same piece of data in multiple places.
If someone places an order, it is only recorded in the order items table. For your example, product id 1, quantity 1. The UNION query SUMs the quantity in the stock table and subtracts the SUM of the quantity in the order items table. For your example, stock (1) - order items (1) = 0.
Canceling an order or canceling one or more item(s) in an order should involve setting a status value in the order items table that would exclude those rows from the UNION query, by adding a condition in the WHERE clause, e.g. status != 'canceled'. For your example, stock (1) - order items (0) = 1.
The suggestion to use a separate order items table and a stock table is because the rest of the data being recorded in each row in these tables is different. These are also two different contexts. A positive quantity ordered, is a reduction in stock and a positive quantity put into stock, increases available stock. By trying to use a single table, you will have unused columns and must deal with when is a quantity treated as positive and when is it treated as negative.