La panadería, etapa 4: los cambios
En resumen: La etapa 4 cambia los datos de la panadería con UPDATE, DELETE y un upsert, y un pedido entregado baja las existencias dentro de una transacción.
Problema
Nada se queda quieto
La harina subió de precio. Ana pagó su pedido. El pedido de Desislava salió hacia su casa. Boris canceló el suyo. Llegó una entrega. Todo esto es cambiar filas que ya existen: UPDATE, DELETE y un insert que también puede ser un cambio.
Cada campo parte de la base completa de la etapa 2.
La idea
Primero SELECT
Los dulces suben un 10 %. Antes del UPDATE ejecutas un SELECT con el mismo WHERE y el valor nuevo como expresión. Así ves qué filas cambiarán y cómo:
Tres filas, solo los dulces. Ahora el mismo WHERE pasa al UPDATE.
Pruébalo
La subida de precios
Sube un 10 % los precios de los dulces (categoría 2), redondeados a 2 decimales.
Solución
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;. Sin WHERE suben también el pan y el café. Con category_id = 1 sube el pan en lugar de los dulces.
Míralo
La cuenta vieja
El precio del cruasán ahora es 1,43 €. El pedido 1, sin embargo, conserva el precio al que compró Ivan:
Por eso en la etapa 1 order_items tiene unit_price: la cuenta de la semana pasada no cambia.
Pruébalo
Pagado
Ana pagó el pedido 6. Cambia su estado a 'paid'.
Solución
UPDATE orders SET status = 'paid' WHERE id = 6;UPDATE orders SET status = 'paid' WHERE id = 6;. Sin WHERE todos los pedidos pasan a pagados, también los entregados y el cancelado. Con WHERE customer_id = 2 cambia además el pedido 2 de Ana, que está entregado: la condición es sobre el pedido, no sobre el cliente.
Un error de escritura no pasa, porque el CHECK de la etapa 1 vigila los estados:
Ejercicio
La entrega
Cuando se entrega el pedido, los productos salen del almacén. Para cada producto del pedido, las existencias bajan en las unidades de la línea. Eso es un UPDATE con una subconsulta que toma las unidades de cada producto.
El pedido 5 (el de Desislava) se entrega. En una transacción baja las existencias de cada producto del pedido en las unidades de la línea y cambia el estado del pedido a 'delivered'.
Solución
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) y WHERE id IN (SELECT product_id FROM order_items WHERE order_id = 5). La subconsulta es correlacionada: products.id es el producto que se está cambiando en ese momento. Sin WHERE la subconsulta da NULL para los productos que no están en el pedido, stock - NULL es NULL y NOT NULL detiene todo el UPDATE. Con stock - 1, los seis cruasanes quitan solo uno.
Más precisamente, para curiosos
SQLite desde la 3.33 y PostgreSQL tienen también UPDATE … FROM: UPDATE products SET stock = stock - i.quantity FROM order_items i WHERE i.order_id = 5 AND i.product_id = products.id;. El resultado es el mismo, y con muchas filas es más rápido.
Míralo
Más de lo que hay
Hay 10 kozunaks. Alguien intenta vender 20:
CHECK (stock >= 0) de la etapa 1 no permite vender lo que no hay.
La idea
Borrar
Un producto que está en pedidos no se puede borrar: la clave foránea de order_items lo vigila.
Un producto que ya no se vende se queda en la base. Quitarlo de la carta es otra columna (active) u otra categoría, no DELETE.
Pruébalo
El pedido cancelado
La panadería no guarda los pedidos cancelados. Borra el pedido 4 junto con sus líneas.
Solución
DELETE FROM orders WHERE id = 4;Basta con DELETE FROM orders WHERE id = 4;: ON DELETE CASCADE en order_items borra sus líneas por sí solo. Si se borran solo las líneas, queda un pedido vacío sin productos. WHERE customer_id = 1 borra los dos pedidos de Ivan.
Más precisamente, para curiosos
Muchos sistemas no borran los pedidos en absoluto: basta con el estado 'cancelled', y el historial queda para los informes y la contabilidad. Eso se llama borrado lógico (soft delete). Aquí es DELETE para que veas CASCADE en acción.
Pruébalo
Una nueva entrega
Llegó una entrega: 20 cruasanes (el producto 4, que ya existe) y un producto nuevo, 25 botellas de boza (será el producto 9, una bebida de 1,50 €). Un mismo INSERT tiene que añadir lo nuevo y aumentar lo existente. Eso es un upsert: INSERT … ON CONFLICT (id) DO UPDATE.
Con un solo INSERT escribe la entrega: (4, 'Cruasán', 2, 1.30, 20) y (9, 'Boza', 3, 1.50, 25). Si el producto existe, sus existencias aumentan en lo entregado.
Solución
INSERT INTO products (id, name, category_id, price, stock) VALUES
(4, 'Cruasán', 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 es la fila que no entró por el conflicto: aquí, los 20 cruasanes. Con SET stock = excluded.stock las existencias pasan a 20, y los 40 antiguos desaparecen. INSERT OR REPLACE hace lo mismo con más sigilo: borra la fila vieja y escribe la nueva.
La idea
Lo que viene
Cada cambio de aquí es una consulta escrita a mano: las existencias bajan cuando alguien se acuerda. La siguiente etapa hace que la base lo haga sola y sea más rápida: un índice, una vista, un trigger y una columna nueva.
Test