UPDATE w SQL: aktualizacja wierszy w SQLite z WHERE i FROM

UPDATE zmienia istniejące wiersze

INSERT dodaje nowe wiersze. UPDATE modyfikuje wiersze, które już istnieją. Schemat jest krótki i warto go zapamiętać:

UPDATE table_name
SET column = value
WHERE condition;

Działający przykład:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT,
    status TEXT DEFAULT 'active'
);

INSERT INTO users (name, email) VALUES
    ('Ada', 'ada@old.com'),
    ('Boris', 'boris@old.com'),
    ('Rosa', 'rosa@old.com');

UPDATE users
SET email = 'ada@new.com'
WHERE id = 1;

SELECT * FROM users;

SET mówi, co się zmienia. WHERE mówi, w których wierszach. Reszta tabeli zostaje nietknięta.

W praktyce klauzula WHERE nie jest opcjonalna

Technicznie WHERE jest opcjonalne. W praktyce pominięcie go to sposób, w jaki początkujący programiści rujnują sobie popołudnie:

UPDATE users SET status = 'inactive';
-- każdy użytkownik jest teraz nieaktywny

Brak filtra oznacza, że pasuje każdy wiersz. SQLite po cichu wykona polecenie. Zawsze pisz najpierw WHERE, a potem SET: już sam ten nawyk zapobiega wielu wypadkom.

Jeśli nie masz pewności, czy WHERE jest poprawne, najpierw uruchom ten sam warunek jako SELECT:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, status TEXT);
INSERT INTO users VALUES (1, 'Ada', 'active'), (2, 'Boris', 'active'), (3, 'Rosa', 'pending');

-- Podejrzyj, których wierszy dotknie zmiana.
SELECT id, name, status FROM users WHERE status = 'pending';

-- Potem uruchom aktualizację z dokładnie tym samym WHERE.
UPDATE users SET status = 'active' WHERE status = 'pending';

SELECT * FROM users;

Ten sam warunek, dwie instrukcje. SELECT to twoja próba na sucho.

Aktualizacja wielu kolumn

Rozdziel przypisania przecinkami w jednym SET. Jeden SET, wiele kolumn:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL,
    stock INTEGER
);

INSERT INTO products VALUES (1, 'Widget', 9.99, 100);

UPDATE products
SET price = 12.99,
    stock = 75,
    name = 'Premium Widget'
WHERE id = 1;

SELECT * FROM products;

Jedno odwołanie do bazy, jeden zmieniony wiersz, trzy zaktualizowane kolumny. Nie pisz trzech osobnych instrukcji UPDATE, gdy wystarczy jedna.

Wyrażenia po prawej stronie =

Wartość po = nie musi być literałem. Może to być dowolne wyrażenie, także takie, które odwołuje się do bieżącej wartości kolumny:

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL, stock INTEGER);
INSERT INTO products VALUES
    (1, 'Widget', 10.00, 50),
    (2, 'Gadget', 20.00, 30),
    (3, 'Gizmo', 15.00, 80);

-- Podnieś ceny o 10%.
UPDATE products
SET price = price * 1.10;

-- Zmniejsz stan magazynowy w jednym wierszu.
UPDATE products
SET stock = stock - 1
WHERE id = 2;

SELECT * FROM products;

price * 1.10 odczytuje istniejącą cenę, mnoży ją i zapisuje wynik z powrotem. SQLite oblicza prawą stronę na podstawie bieżących wartości wiersza, zanim zadziała którekolwiek przypisanie tej instrukcji, więc możesz bezpiecznie odwoływać się do kilku kolumn:

UPDATE products SET price = price * 1.10, stock = stock + price;
-- 'price' po prawej stronie to tutaj STARA cena, a nie ta właśnie zaktualizowana.

UPDATE ... FROM: pobieranie wartości z innej tabeli

Od SQLite 3.33 UPDATE obsługuje klauzulę FROM do aktualizacji między tabelami. To najczytelniejszy sposób na synchronizację danych między tabelami:

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, total_spent REAL DEFAULT 0);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount REAL);

INSERT INTO customers (name) VALUES ('Ada'), ('Boris'), ('Rosa');
INSERT INTO orders (customer_id, amount) VALUES
    (1, 50.00), (1, 30.00),
    (2, 100.00),
    (3, 25.00), (3, 75.00);

UPDATE customers
SET total_spent = totals.sum_amount
FROM (
    SELECT customer_id, SUM(amount) AS sum_amount
    FROM orders
    GROUP BY customer_id
) AS totals
WHERE customers.id = totals.customer_id;

SELECT * FROM customers;

Podzapytanie liczy sumy dla każdego klienta; zewnętrzne UPDATE łączy te wyniki z customers po id. Bez UPDATE ... FROM trzeba by pisać podzapytanie skorelowane dla każdej kolumny, co daje dużo więcej szumu.

Kilka zasad, o których warto pamiętać:

• Tabela docelowa stoi po UPDATE, a nie na liście FROM.
• Połączenie wykonuje klauzula WHERE: nie ma tu słowa kluczowego ON.
• Jeśli połączenie może dopasować więcej niż jeden wiersz z FROM, wynik jest nieokreślony. Upewnij się, że klucze połączenia dają najwyżej jedno dopasowanie na każdy wiersz docelowy.

RETURNING: zobacz, co się zmieniło

SQLite (3.35+) pozwala, żeby UPDATE zwracało zmodyfikowane wiersze w tej samej instrukcji. Przydaje się, gdy aplikacja potrzebuje wartości po aktualizacji bez dodatkowego SELECT:

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
INSERT INTO products VALUES (1, 'Widget', 10.00), (2, 'Gadget', 20.00);

UPDATE products
SET price = price * 0.9
WHERE price > 15
RETURNING id, name, price;

Dostajesz wiersze, które faktycznie zostały zmienione, z ich nowymi wartościami. Oszczędzasz jedno odwołanie do bazy i eliminujesz całą klasę wyścigów w kodzie współbieżnym. Klauzuli RETURNING poświęcamy osobną stronę w dalszej części tego rozdziału.

UPDATE OR REPLACE: obsługa konfliktów ograniczeń

Jeśli aktualizacja naruszyłaby ograniczenie UNIQUE, domyślnie instrukcja zostaje przerwana z błędem. Klauzula OR pozwala wybrać inną politykę:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    name TEXT
);

INSERT INTO users VALUES
    (1, 'ada@x.com', 'Ada'),
    (2, 'boris@x.com', 'Boris');

-- To zakończyłoby się naruszeniem ograniczenia UNIQUE:
-- UPDATE users SET email = 'boris@x.com' WHERE id = 1;

-- OR REPLACE usuwa kolidujący wiersz, a potem wykonuje aktualizację.
UPDATE OR REPLACE users
SET email = 'boris@x.com'
WHERE id = 1;

SELECT * FROM users;

Dostępne opcje to OR ABORT (domyślna), OR REPLACE, OR IGNORE, OR FAIL i OR ROLLBACK. Niebezpieczna jest REPLACE: usuwa kolidujący wiersz, co może pociągnąć za sobą kaskadę przez klucze obce. Używaj jej tylko wtedy, gdy naprawdę masz na myśli „jeśli istnieje już wiersz z tą unikalną wartością, wyrzuć go”.

Do większości operacji typu upsert czytelniejsza jest dedykowana składnia INSERT ... ON CONFLICT. Ma ona własną stronę.

Ryzykowne aktualizacje opakuj w transakcję

Gdy modyfikujesz dużo wierszy albo uruchamiasz kilka instrukcji UPDATE, które muszą się udać razem, opakuj je w transakcję. Jeśli coś pójdzie nie tak, możesz wrócić do wcześniejszego stanu:

CREATE TABLE accounts (id INTEGER PRIMARY KEY, name TEXT, balance REAL);
INSERT INTO accounts VALUES (1, 'Ada', 100.00), (2, 'Boris', 50.00);

BEGIN;
    UPDATE accounts SET balance = balance - 25 WHERE id = 1;
    UPDATE accounts SET balance = balance + 25 WHERE id = 2;
COMMIT;

SELECT * FROM accounts;

Jeśli druga instrukcja się nie powiedzie (na przykład zadziała ograniczenie), ROLLBACK cofa pierwszą. Bez transakcji przelew zostałby w połowie: Ada ma 25 mniej, a u Borisa bez zmian. Transakcje mają później własny rozdział; na razie wystarczy wiedzieć, że istnieją i że masowe aktualizacje prawie zawsze powinny się w nich znaleźć.

Typowe pułapki

Krótka lista rzeczy, które dają ludziom w kość:

• Zapomniane WHERE: aktualizuje każdy wiersz. Przeczytaj instrukcję na głos przed uruchomieniem.
• Zły operator w WHERE: WHERE status = NULL nie pasuje do niczego. Użyj IS NULL. Omawiamy to na stronie o operatorach.
• Aktualizacja podzapytaniem, które zwraca więcej niż jeden wiersz, gdy oczekujesz jednego. Użyj LIMIT 1 albo agregacji w podzapytaniu, bo inaczej dostaniesz błędy albo zaskakujące wyniki.
• Mylenie UPDATE OR REPLACE z UPSERT. OR REPLACE usuwa kolidujące wiersze. INSERT ... ON CONFLICT DO UPDATE modyfikuje je na miejscu. To różne operacje.

Dalej: DELETE

UPDATE modyfikuje wiersze; DELETE je usuwa. Obowiązuje ta sama dyscyplina z WHERE, a ten sam nawyk „najpierw uruchom SELECT” uchroni cię przed tymi samymi katastrofami. O tym jest następna strona.