Die Bäckerei, Etappe 4: die Änderungen
Kurz gesagt: Etappe 4 ändert die Daten der Bäckerei mit UPDATE, DELETE und einem Upsert, und eine gelieferte Bestellung senkt den Lagerbestand innerhalb einer Transaktion.
Problem
Nichts bleibt, wie es ist
Das Mehl wurde teurer. Ana hat ihre Bestellung bezahlt. Desislavas Bestellung ist zu ihr unterwegs. Boris hat seine storniert. Eine Lieferung ist gekommen. All das heißt, bestehende Zeilen zu ändern: UPDATE, DELETE und ein Insert, das auch eine Änderung sein kann.
Jedes Feld beginnt mit der vollen Datenbank aus Etappe 2.
Die Idee
Erst SELECT
Die Teilchen werden 10 % teurer. Vor dem UPDATE führst du ein SELECT mit demselben WHERE und dem neuen Wert als Ausdruck aus. So siehst du, welche Zeilen sich ändern und wie:
Drei Zeilen, nur die Teilchen. Jetzt kommt dasselbe WHERE in das UPDATE.
Probier es aus
Die Preiserhöhung
Erhöhe die Preise der Teilchen (Kategorie 2) um 10 %, auf 2 Stellen gerundet.
Lösung
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;. Ohne WHERE werden auch das Brot und der Kaffee teurer. Mit category_id = 1 wird das Brot teurer statt der Teilchen.
Sieh es dir an
Die alte Rechnung
Der Preis des Croissants ist jetzt 1,43 €. Bestellung 1 behält aber den Preis, zu dem Ivan gekauft hat:
Darum hat order_items in Etappe 1 das unit_price: Die Rechnung von letzter Woche ändert sich nicht.
Probier es aus
Bezahlt
Ana hat Bestellung 6 bezahlt. Ändere ihren Status auf 'paid'.
Lösung
UPDATE orders SET status = 'paid' WHERE id = 6;UPDATE orders SET status = 'paid' WHERE id = 6;. Ohne WHERE werden alle Bestellungen bezahlt, auch die gelieferten und die stornierte. Mit WHERE customer_id = 2 ändert sich auch Anas gelieferte Bestellung 2: Die Bedingung gilt der Bestellung, nicht dem Kunden.
Ein Tippfehler kommt nicht durch, denn das CHECK aus Etappe 1 bewacht die Status:
Übung
Die Lieferung
Wird die Bestellung geliefert, verlassen die Produkte das Lager. Für jedes Produkt der Bestellung sinkt der Lagerbestand um die Stückzahl in der Zeile. Das ist ein UPDATE mit einer Unterabfrage, die für jedes Produkt die Stückzahl holt.
Bestellung 5 (Desislavas) wird geliefert. Senke in einer Transaktion den Lagerbestand jedes Produkts darin um die Stückzahl in der Zeile und ändere den Status der Bestellung auf 'delivered'.
Lösung
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) und WHERE id IN (SELECT product_id FROM order_items WHERE order_id = 5). Die Unterabfrage ist korreliert: products.id ist das Produkt, das gerade geändert wird. Ohne WHERE liefert die Unterabfrage für die Produkte außerhalb der Bestellung NULL, stock - NULL ist NULL, und NOT NULL stoppt das ganze UPDATE. Mit stock - 1 nehmen die sechs Croissants nur eines weg.
Genauer, für Neugierige
SQLite seit 3.33 und PostgreSQL haben auch UPDATE … FROM: UPDATE products SET stock = stock - i.quantity FROM order_items i WHERE i.order_id = 5 AND i.product_id = products.id;. Das Ergebnis ist dasselbe, und bei vielen Zeilen ist es schneller.
Sieh es dir an
Mehr, als da ist
Es gibt 10 Kozunaks. Jemand versucht, 20 zu verkaufen:
CHECK (stock >= 0) aus Etappe 1 erlaubt es nicht, etwas zu verkaufen, das nicht da ist.
Die Idee
Löschen
Ein Produkt, das in Bestellungen vorkommt, lässt sich nicht löschen: Der Fremdschlüssel in order_items bewacht es.
Ein Produkt, das nicht mehr verkauft wird, bleibt in der Datenbank. Es aus der Speisekarte zu nehmen ist eine andere Spalte (active) oder eine andere Kategorie, nicht DELETE.
Probier es aus
Die stornierte Bestellung
Die Bäckerei behält stornierte Bestellungen nicht. Lösche Bestellung 4 zusammen mit ihren Zeilen.
Lösung
DELETE FROM orders WHERE id = 4;DELETE FROM orders WHERE id = 4; genügt: ON DELETE CASCADE in order_items löscht ihre Zeilen von selbst. Löscht man nur die Zeilen, bleibt eine leere Bestellung ohne Produkte zurück. WHERE customer_id = 1 löscht Ivans zwei Bestellungen.
Genauer, für Neugierige
Viele Systeme löschen Bestellungen gar nicht: Der Status 'cancelled' genügt, und die Geschichte bleibt für die Berichte und die Buchhaltung. Das nennt man Soft Delete. Hier ist es DELETE, damit du CASCADE bei der Arbeit siehst.
Probier es aus
Eine neue Lieferung
Eine Lieferung ist gekommen: 20 Croissants (Produkt 4, das es schon gibt) und ein neues Produkt, 25 Flaschen Boza (es wird Produkt 9, ein Getränk für 1,50 €). Ein und dasselbe INSERT muss das Neue hinzufügen und das Vorhandene erhöhen. Das ist ein Upsert: INSERT … ON CONFLICT (id) DO UPDATE.
Schreibe die Lieferung mit einem INSERT: (4, 'Croissant', 2, 1.30, 20) und (9, 'Boza', 3, 1.50, 25). Gibt es das Produkt schon, wächst sein Lagerbestand um das Gelieferte.
Lösung
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 ist die Zeile, die wegen des Konflikts nicht hineinkam: hier die 20 Croissants. Mit SET stock = excluded.stock wird der Lagerbestand 20, und die alten 40 verschwinden. INSERT OR REPLACE macht dasselbe stiller: Es löscht die alte Zeile und schreibt die neue.
Die Idee
Was als Nächstes kommt
Jede Änderung hier ist eine von Hand geschriebene Abfrage: Der Lagerbestand sinkt, wenn jemand daran denkt. Die nächste Etappe lässt die Datenbank das von selbst tun und macht sie schneller: ein Index, eine View, ein Trigger und eine neue Spalte.
Test