Zapewne miałeś do czynienia z potrzebą wstawienia rekordu jeśli go brak i ewentualnej aktualizacji jeśli takowy istnieje. To takie dwie pieczenie na jednym. Ta zabawa nad ogniskiem w języku SQL nazywa się UPSERT (UPDATE+INSERT). Ale dziś nie o genezie tego słowa, ale praktycznym użyciu i to nie w przypadku aktualizacji jednego rekordu, ale całej masy naraz.
Krokiem dżdżownicy.
Zacznijmy od przykładowej tabeli. Będzie zawierać trzy pola: email (z indeksem unikalnym), telefon oraz adres.
W przypadku dodania kilku rekordów w jednym INSERT, zapytanie będzie wyglądać następująco:
INSERT INTO users VALUES
('aaa@aaa.com', '000111222', 'ul. Ananasowa 1'),
('bbb@bbb.com', '333444555', 'ul. Brzoskwiniowa 2'),
('ccc@ccc.com', '666777888', 'ul. Cytrynowa 3')
Jeśli chcielibyśmy teraz wstawić jeden rekord i ewentualnie jeśli istnieje zaktualizować go, to w przypadku MySQL użyjemy:
INSERT INTO users VALUES
('aaa@aaa.com', '222111000', 'ul. Anyżowa 1')
ON DUPLICATE KEY
UPDATE telefon='222111000', adres='ul. Anyżowa 1'
W przypadku SQLite:
INSERT INTO users VALUES
('aaa@aaa.com', '222111000', 'ul. Anyżowa 1')
ON CONFLICT (email)
DO UPDATE SET telefon='222111000', adres='ul. Anyżowa 1'
Zauważyłeś pewnie, że w przypadku UPDATE powtórzone są wartości z INSERT. Nie bardzo ma to sens, a jako, że jesteśmy leniwi, to warto by było uprościć zapytanie i nie powtarzać tych samych wartości, lecz użyć właśnie te z INSERT.
Nie papuguj.
I na ten problem mamy rozwiązanie. W MySQL mamy funkcję VALUES, której argumentem jest nazwa kolumny, której wartość odpowiada wartości w INSERT.
INSERT INTO users VALUES
('aaa@aaa.com', '222111000', 'ul. Anyżowa 1')
ON DUPLICATE KEY
UPDATE telefon=VALUES(telefon), adres=VALUES(adres)
SQLite używa natomiast prefiksu excluded:
INSERT INTO users VALUES
('aaa@aaa.com', '222111000', 'ul. Anyżowa 1')
ON CONFLICT (email)
DO UPDATE SET telefon=excluded.telefon, adres=excluded.adres
Gąski, gąski do domu.
Mając teraz 100 rekordów do aktualizacji, zauważasz chyba, że rozwiązanie jest pod ręką. Nie byłoby sensu powtarzać wartości dla tych 100 rekordów ponownie w części dotyczącej UPDATE.
MySQL:
INSERT INTO users VALUES
('aaa@aaa.com', '222111000', 'ul. Anyżowa 1'),
('bbb@bbb.com', '555444333', 'ul. Bananowa 2'),
('ddd@ddd.com', '999000111', 'ul. Dyniowa 4')
ON DUPLICATE KEY
UPDATE telefon=VALUES(telefon), adres=VALUES(adres)
SQLite:
INSERT INTO users VALUES
('aaa@aaa.com', '222111000', 'ul. Anyżowa 1'),
('bbb@bbb.com', '555444333', 'ul. Bananowa 2'),
('ddd@ddd.com', '999000111', 'ul. Dyniowa 4')
ON CONFLICT (email)
DO UPDATE SET telefon=excluded.telefon, adres=excluded.adres
I tym sposobem jesteśmy w ogródku. Kolejne przygody, w kolejnym wpisie, na który Cię zapraszam!
Przydatne linki:
UPSERT w MySQL
Funkcja VALUES w MySQL
UPSERT w SQLite