The bakery, stage 4: the changes
In short: Stage 4 changes the bakery data with UPDATE, DELETE and an upsert, and a delivered order lowers the stock inside a transaction.
Problem
Nothing stays put
Flour got dearer. Ana paid for her order. Desislava's order set off to her. Boris cancelled his. A delivery came. All of this is changing rows that already exist: UPDATE, DELETE and an insert that may also be a change.
Every field starts from the full database from stage 2.
The idea
SELECT first
The pastries get 10% dearer. Before the UPDATE you run a SELECT with the same WHERE and the new value as an expression. That way you see which rows will change and how:
Three rows, only the pastries. Now the same WHERE goes into the UPDATE.
Try it
The price rise
Raise the prices of the pastries (category 2) by 10%, rounded to 2 places.
Solution
UPDATE products
SET price = ROUND(price * 1.1, 2)
WHERE category_id = 2;UPDATE products SET price = ROUND(price * 1.1, 2) WHERE category_id = 2;. Without WHERE the bread and the coffee get dearer too. With category_id = 1 the bread gets dearer instead of the pastries.
See it
The old bill
The price of the croissant is now €1.43. Order 1, though, keeps the price at which Ivan bought:
That is why in stage 1 order_items has unit_price: last week's bill does not change.
Try it
Paid
Ana paid for order 6. Change its status to 'paid'.
Solution
UPDATE orders SET status = 'paid' WHERE id = 6;UPDATE orders SET status = 'paid' WHERE id = 6;. Without WHERE all the orders become paid, the delivered ones and the cancelled one too. With WHERE customer_id = 2 Ana's delivered order 2 changes as well: the condition is on the order, not the customer.
A typo does not get through, because the CHECK from stage 1 guards the statuses:
Exercise
The delivery
When the order is delivered, the products leave the store. For every product of the order the stock goes down by the pieces in the row. That is an UPDATE with a subquery that takes the pieces for each product.
Order 5 (Desislava's) is delivered. In one transaction lower the stock of every product in it by the pieces in the row and change the status of the order to 'delivered'.
Solution
BEGIN;
UPDATE products
SET stock = stock - (SELECT quantity FROM order_items
WHERE order_id = 5 AND product_id = products.id)
WHERE id IN (SELECT product_id FROM order_items WHERE order_id = 5);
UPDATE orders SET status = 'delivered' WHERE id = 5;
COMMIT;SET stock = stock - (SELECT quantity FROM order_items WHERE order_id = 5 AND product_id = products.id) and WHERE id IN (SELECT product_id FROM order_items WHERE order_id = 5). The subquery is correlated: products.id is the product being changed at the moment. Without WHERE the subquery gives NULL for the products outside the order, stock - NULL is NULL and NOT NULL stops the whole UPDATE. With stock - 1 the six croissants take off only one.
More precisely, if you are curious
SQLite since 3.33 and PostgreSQL also have UPDATE … FROM: UPDATE products SET stock = stock - i.quantity FROM order_items i WHERE i.order_id = 5 AND i.product_id = products.id;. The result is the same, and for many rows it is faster.
See it
More than there is
There are 10 kozunaks. Someone tries to sell 20:
CHECK (stock >= 0) from stage 1 does not allow selling something that is not there.
The idea
Deleting
A product that is in orders cannot be deleted: the foreign key in order_items guards it.
A product that is no longer sold stays in the database. Taking it off the menu is another column (active) or another category, not DELETE.
Try it
The cancelled order
The bakery does not keep cancelled orders. Delete order 4 together with its rows.
Solution
DELETE FROM orders WHERE id = 4;DELETE FROM orders WHERE id = 4; is enough: ON DELETE CASCADE in order_items deletes its rows on its own. If only the rows are deleted, an empty order without products is left. WHERE customer_id = 1 deletes Ivan's two orders.
More precisely, if you are curious
Many systems do not delete orders at all: the status 'cancelled' is enough, and the history stays for the reports and the accounts. This is called a soft delete. Here it is DELETE so that you see CASCADE at work.
Try it
A new delivery
A delivery came: 20 croissants (product 4, which already exists) and a new product, 25 bottles of boza (it will be product 9, a drink for €1.50). One and the same INSERT has to add the new one and increase the existing one. This is an upsert: INSERT … ON CONFLICT (id) DO UPDATE.
With one INSERT write the delivery: (4, 'Croissant', 2, 1.30, 20) and (9, 'Boza', 3, 1.50, 25). If the product exists, its stock grows by what was delivered.
Solution
INSERT INTO products (id, name, category_id, price, stock) VALUES
(4, 'Croissant', 2, 1.30, 20),
(9, 'Boza', 3, 1.50, 25)
ON CONFLICT (id) DO UPDATE SET stock = stock + excluded.stock;ON CONFLICT (id) DO UPDATE SET stock = stock + excluded.stock;. excluded is the row that did not go in because of the conflict: here the 20 croissants. With SET stock = excluded.stock the stock becomes 20, and the old 40 disappear. INSERT OR REPLACE does the same more quietly: it deletes the old row and writes the new one.
The idea
What comes next
Every change here is a query written by hand: the stock goes down when someone remembers. The next stage makes the database do it on its own and be faster: an index, a view, a trigger and a new column.
Test