UPSERT ve RETURNING: ekle ya da güncelle

Tek komutla “varsa güncelle, yoksa ekle”: `INSERT ... ON CONFLICT`, `excluded`, `INSERT OR REPLACE` tehlikesi, değişen satırları `RETURNING` ile geri almak, PostgreSQL'de `MERGE`, MySQL'de `ON DUPLICATE KEY UPDATE` ve güvenle tekrarlanabilen yüklemeler.

  • İleri
  • 20 dk
  • 12 soru
  • Ders 11 / 28

Bu derste öğreneceklerin

  • ON CONFLICT ... DO NOTHING ve DO UPDATE SET ... = excluded.... ile upsert yazmak
  • INSERT OR REPLACE komutunun neden upsert olmadığını açıklamak
  • INSERT, UPDATE ve DELETE komutlarından RETURNING ile sonuç almak
  • MERGE ve ON DUPLICATE KEY UPDATE komutlarını tanımak ve idempotent bir yükleme kurmak

Tedarikçi her gece bir fiyat listesi gönderiyor: bazı ürünler mağazada zaten var, bazıları yeni. Saf yol iki adımdır: SELECT ile kontrol et, sonra INSERT ya da UPDATE. Bu, veritabanına iki gidiş-dönüştür ve daha kötüsü bir yarış durumudur: iki süreç aynı anda “yok” görür, ikisi de eklemeye çalışır ve biri UNIQUE hatası alır. Upsert (update + insert) bu kararı veritabanının içinde, tek ve atomik bir komutla verir. «INSERT, UPDATE, DELETE: verileri değiştirme» dersinde bir tabloda adı geçmişti; şimdi onu, değişen satırları hemen geri döndüren RETURNING ile birlikte iyice öğreniyoruz.

ON CONFLICT: DO NOTHING ve DO UPDATE

UpsertTanım

Eklenen satır benzersiz bir anahtarla çakıştığında (conflict) ne yapılacağını söyleyen bir INSERT: hiçbir şey (DO NOTHING) ya da var olan satırı güncellemek (DO UPDATE). Çakışma yalnızca bir PRIMARY KEY ya da UNIQUE kısıtlaması üzerinde olabilir.

Alıştırma veritabanında products.name benzersiz değil; bu yüzden önce benzersiz bir indeks oluşturuyoruz. Artık ikinci bir Laptop sıradan bir INSERT ile hata verir:

SQL
CREATE UNIQUE INDEX idx_products_name ON products (name);

INSERT INTO products (name, category, price, stock)
VALUES ('Laptop', 'Electronics', 1399, 5);
Beklenen çıktı
Error: UNIQUE constraint failed: products.name

ON CONFLICT (name) DO NOTHING ile çakışan satır sessizce atılır, diğerleri eklenir. Parantezdeki sütunlar çakışma hedefidir ve var olan bir benzersizlik kısıtlamasına karşılık gelmelidir:

SQL
CREATE UNIQUE INDEX idx_products_name ON products (name);

INSERT INTO products (name, category, price, stock)
VALUES ('Laptop', 'Electronics', 1399, 5),
       ('Tablet', 'Electronics', 520, 10)
ON CONFLICT (name) DO NOTHING;

SELECT id, name, price, stock FROM products
WHERE name IN ('Laptop', 'Tablet') ORDER BY id;
Beklenen çıktı
id | name | price | stock
1 | Laptop | 1450 | 8
11 | Tablet | 520 | 10
Eski Laptop olduğu gibi kaldı, Tablet eklendi.

DO UPDATE SET içinde iki satır görünür. **excluded, eklenmeye çalışılan ama çakışma yüzünden “dışarıda kalan” satırdır; tablonun adı (ya da öneksiz bir sütun) ise veritabanında zaten bulunan satırdır. Aşağıda fiyat yenisiyle değiştiriliyor, stok ise eskisine ekleniyor**:

SQL
CREATE UNIQUE INDEX idx_products_name ON products (name);

INSERT INTO products (name, category, price, stock)
VALUES ('Laptop', 'Electronics', 1399, 5),
       ('Tablet', 'Electronics', 520, 10)
ON CONFLICT (name) DO UPDATE
SET price = excluded.price,
    stock = products.stock + excluded.stock;

SELECT id, name, price, stock FROM products
WHERE name IN ('Laptop', 'Tablet') ORDER BY id;
Beklenen çıktı
id | name | price | stock
1 | Laptop | 1399 | 13
11 | Tablet | 520 | 10
Laptop id değerini korudu: fiyat 1399, stok 8 + 5 = 13.

Klasik bir kullanım sayaçtır: sayfa ilk kez açıldığında satır oluşur, sonra yalnızca sayı artar. Programın “satır var mı?” diye sormasına gerek kalmaz:

SQL
CREATE TABLE page_views (page TEXT PRIMARY KEY, views INTEGER NOT NULL);

INSERT INTO page_views VALUES ('/sql', 1)
ON CONFLICT (page) DO UPDATE SET views = views + 1;
INSERT INTO page_views VALUES ('/sql', 1)
ON CONFLICT (page) DO UPDATE SET views = views + 1;
INSERT INTO page_views VALUES ('/python', 1)
ON CONFLICT (page) DO UPDATE SET views = views + 1;
INSERT INTO page_views VALUES ('/sql', 1)
ON CONFLICT (page) DO UPDATE SET views = views + 1;

SELECT * FROM page_views ORDER BY page;
Beklenen çıktı
page | views
/python | 1
/sql | 3

Eski veri yenisinin üzerine yazmasın

Problem

Depo sistemi stok mesajları gönderiyor ama bunlar bazen karışık sırayla geliyor: önce 10:05 mesajı (stok 7), sonra 10:00 mesajı (stok 9). stock_levels(sku PRIMARY KEY, qty, updated_at) tablosunda her zaman en yeni stok nasıl tutulur?

Çözümü gösterÇözümü gizle

Çözüm

1) DO UPDATE bölümüne bir koşul ekle: ON CONFLICT (sku) DO UPDATE SET qty = excluded.qty, updated_at = excluded.updated_at WHERE excluded.updated_at > stock_levels.updated_at.
2) 10:05 mesajı gelince satır oluşur ya da güncellenir: stok 7.
3) 10:00 mesajı gelince WHERE yanlıştır (10:00, 10:05'ten sonra değil); satır değişmez.
4) Sonuç: stok 7 kalır. WHERE olmasaydı en son gelen mesaj (9) kazanırdı.

Okumaya devam etmek için ücretsiz kaydol

Dersin geri kalan bölümleri, test ve notlar Educora hesabıyla açılır. Kayıt bir dakika sürer; ilerlemen tüm cihazlarında saklanır.

INSERT OR REPLACE bir upsert değildir

SQLite'ta (ve MySQL'de REPLACE INTO) daha kısa bir yazım var ama başka bir iş yapar: çakışmada eski satırı siler ve yenisini ekler. Listede olmayan sütunlar NULL (ya da DEFAULT) olur, satır yeni bir id alır ve ona başvuran siparişler “öksüz” kalır:

SQL
CREATE UNIQUE INDEX idx_products_name ON products (name);

INSERT OR REPLACE INTO products (name, price)
VALUES ('Laptop', 1399);

SELECT p.id, p.name, p.category, p.price, p.stock,
       (SELECT COUNT(*) FROM orders AS o WHERE o.product_id = p.id) AS orders
FROM products AS p
WHERE p.name = 'Laptop';
Beklenen çıktı
id | name | category | price | stock | orders
11 | Laptop | NULL | 1399 | NULL | 0
Kategori ve stok kayboldu, id 1'den 11'e değişti; 1 numaralı sipariş artık var olmayan bir ürüne bakıyor.

RETURNING: değişen satırları hemen geri almak

Yeni satırın id değeri, veritabanının hesapladığı bir değer, silinen satırlar: genellikle bunlar için ikinci bir SELECT yazılır. RETURNING (SQLite 3.35'ten beri, PostgreSQL'de uzun zamandır) INSERT, UPDATE ve DELETE komutlarının sonuna yazılır ve değişen her satır için belirtilen sütunları döndürür; ifadeler ve takma adlar dahil, tıpkı bir SELECT listesi gibi:

SQL
INSERT INTO students (first_name, last_name, age, grade, city)
VALUES ('Nərmin', 'Səlimova', 15, 9, 'Bakı'),
       ('Rauf', 'Məlikov', 16, 10, 'Gəncə')
RETURNING id, first_name || ' ' || last_name AS student;
Beklenen çıktı
id | student
13 | Nərmin Səlimova
14 | Rauf Məlikov
Program yeni id değerlerini ek sorgu olmadan alır.
SQL
UPDATE products
SET price = ROUND(price * 0.9, 2)
WHERE category = 'Electronics' AND stock > 0
RETURNING name, price AS new_price;
Beklenen çıktı
name | new_price
Laptop | 1305
Smartphone | 809.99
Headphones | 108.45
RETURNING değişiklikten sonraki değerleri gösterir (stoğu 0 olan Monitor değişmedi).
SQL
CREATE UNIQUE INDEX idx_products_name ON products (name);

INSERT INTO products (name, category, price, stock)
VALUES ('Laptop', 'Electronics', 1399, 5),
       ('Tablet', 'Electronics', 520, 10)
ON CONFLICT (name) DO UPDATE
SET price = excluded.price, stock = products.stock + excluded.stock
RETURNING id, name, price, stock;
Beklenen çıktı
id | name | price | stock
1 | Laptop | 1399 | 13
11 | Tablet | 520 | 10
Upsert + RETURNING: hem güncellenen hem eklenen satır son hâliyle döner.
SQL
CREATE TABLE orders_archive (LIKE orders);

WITH moved AS (
  DELETE FROM orders
  WHERE order_date < '2025-02-01'
  RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM moved
RETURNING id, order_date;

Beklenen çıktı

 id | order_date
----+------------
  1 | 2025-01-15
  2 | 2025-01-15
(2 rows)
PostgreSQL: eski siparişler tek komutla arşive taşınıyor.

MERGE ve ON DUPLICATE KEY UPDATE

MERGE, SQL standardının bir komutudur: hedef tabloyu bir kaynakla karşılaştırır ve her durumda ne yapılacağını ayrı ayrı yazar: WHEN MATCHED [AND koşul] THEN UPDATE (ya da DELETE), WHEN NOT MATCHED THEN INSERT. SQL Server ve Oracle'da uzun zamandır, PostgreSQL'de 15. sürümden beri vardır; 17. sürüm RETURNING, merge_action() ve WHEN NOT MATCHED BY SOURCE (kaynakta olmayan satırlar) ekledi. Sonuç PGlite'ta (PostgreSQL 18) denendi:

SQL
MERGE INTO products AS p
USING price_feed AS f
ON p.name = f.name
WHEN MATCHED AND p.price <> f.price THEN
  UPDATE SET price = f.price
WHEN NOT MATCHED THEN
  INSERT (name, price) VALUES (f.name, f.price)
RETURNING merge_action() AS action, p.id, p.name, p.price;

Beklenen çıktı

 action | id |  name  |  price
--------+----+--------+---------
 UPDATE |  1 | Laptop | 1399.00
 INSERT | 11 | Tablet |  520.00
(2 rows)
Headphones fiyatı aynı; hiçbir dala düşmedi.

Bir incelik: PostgreSQL'de tek bir INSERT ... ON CONFLICT DO UPDATE aynı satırı iki kez değiştiremez. Kaynakta aynı anahtar iki kez varsa komut hata verir; önce kopyaları birleştir (GROUP BY). SQLite ise böyle bir komutu çalıştırır ve ikinci satır birincinin üzerine yazar. Ayrıca MERGE, eş zamanlı INSERT işlemlerinde benzersizlik hatasıyla durabilir; basit bir “anahtara göre upsert” için PostgreSQL belgeleri INSERT ... ON CONFLICT önerir.

SQL
INSERT INTO products (name, price, stock)
VALUES ('Tablet', 520, 5),
       ('Tablet', 499, 3)
ON CONFLICT (name) DO UPDATE
SET stock = products.stock + excluded.stock;

Beklenen çıktı

ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

MySQL'in kendi sözdizimi vardır: ON DUPLICATE KEY UPDATE. Çakışma hedefi yazılmaz; herhangi bir PRIMARY KEY ya da UNIQUE indeks onu tetikler. 8.0.19'dan beri önerilen satıra bir takma adla başvurulur (AS new); eski VALUES(sütun) fonksiyonu 8.0.20'den beri kullanımdan kaldırılmış sayılır. MySQL eklemede 1, güncellemede 2 “etkilenen satır” bildirir.

SQL
-- MySQL 8.0.19+
INSERT INTO products (name, category, price, stock)
VALUES ('Laptop', 'Electronics', 1399, 5),
       ('Tablet', 'Electronics', 520, 10) AS new
ON DUPLICATE KEY UPDATE
  price = new.price,
  stock = products.stock + new.stock;
MySQL: aynı upsert, çakışma hedefi olmadan.
GörevSQLitePostgreSQLMySQLSQL Server
Varsa güncelle, yoksa ekleON CONFLICT … DO UPDATEON CONFLICT … DO UPDATE, MERGE (15+)ON DUPLICATE KEY UPDATEMERGE
Kopyaları atlamakON CONFLICT DO NOTHINGON CONFLICT DO NOTHINGINSERT IGNORE (!)MERGE / NOT EXISTS
Değişen satırları döndürmekRETURNING (3.35+)RETURNINGyokOUTPUT
INSERT IGNORE yalnızca kopyaları değil, başka birçok hatayı da uyarıya çevirir; dikkatle kullan.

Mini proje: tekrar çalıştırılabilen gece yüklemesi

İdempotent işlemTanım

Bir kez de on kez de çalışsa aynı sonucu veren işlem. Yükleme yarıda kesilirse onu yeniden başlatmak yeter; kopya ve çift sayım olmaz.

  1. Ara tablo

    Tedarikçinin dosyası önce price_feed tablosuna yüklenir.

  2. Upsert

    INSERT ... SELECT ... WHERE true ON CONFLICT (name) DO UPDATE. SQLite'ta SELECT sonrasındaki WHERE önemlidir: o olmazsa ON sözcüğü bir birleştirmenin ON bölümü gibi okunur ve near "DO": syntax error çıkar.

  3. Yalnızca değişeni güncelle

    WHERE products.price IS NOT excluded.price: aynı fiyat yeniden yazılmaz, günlükler ve tetikleyiciler boşuna çalışmaz.

  4. Rapor ve deneme

    RETURNING neyin değiştiğini gösterir; ikinci çalıştırma 0 satır değiştirmelidir.

SQL
CREATE UNIQUE INDEX idx_products_name ON products (name);
CREATE TABLE price_feed (name TEXT, category TEXT, price REAL);
INSERT INTO price_feed VALUES
  ('Laptop',     'Electronics', 1399),
  ('Headphones', 'Electronics', 120.5),
  ('Tablet',     'Electronics', 520);

INSERT INTO products (name, category, price, stock)
SELECT name, category, price, 0 FROM price_feed WHERE true
ON CONFLICT (name) DO UPDATE
SET price = excluded.price
WHERE products.price IS NOT excluded.price
RETURNING id, name, price;
Beklenen çıktı
id | name | price
1 | Laptop | 1399
11 | Tablet | 520
İlk çalıştırma: Laptop güncellendi, Tablet eklendi, Headphones (fiyat aynı) değişmedi.
SQL
CREATE UNIQUE INDEX idx_products_name ON products (name);
CREATE TABLE price_feed (name TEXT, category TEXT, price REAL);
INSERT INTO price_feed VALUES
  ('Laptop',     'Electronics', 1399),
  ('Headphones', 'Electronics', 120.5),
  ('Tablet',     'Electronics', 520);

INSERT INTO products (name, category, price, stock)
SELECT name, category, price, 0 FROM price_feed WHERE true
ON CONFLICT (name) DO UPDATE
SET price = excluded.price
WHERE products.price IS NOT excluded.price;   -- first run
INSERT INTO products (name, category, price, stock)
SELECT name, category, price, 0 FROM price_feed WHERE true
ON CONFLICT (name) DO UPDATE
SET price = excluded.price
WHERE products.price IS NOT excluded.price;   -- second run, the same file

SELECT changes() AS rows_changed_by_second_run,
       (SELECT COUNT(*) FROM products) AS products;
Beklenen çıktı
rows_changed_by_second_run | products
0 | 11
changes(), son komutun değiştirdiği satır sayısıdır: ikinci çalıştırma hiçbir şeyi değiştirmedi, ürün sayısı yine 11.

Alıştırma

Ziyaret sayacı: daily_visits (page TEXT PRIMARY KEY, visits INTEGER NOT NULL) tablosunu oluştur. Bir sayfaya bir ziyareti kaydeden (yoksa 1 ile oluşturan, varsa 1 artıran) bir upsert yaz ve onu sırayla /home, /sql, /home sayfaları için çalıştır. Tabloyu page sırasıyla göster.

Alıştırma · SQL
CREATE TABLE daily_visits (page TEXT PRIMARY KEY, visits INTEGER NOT NULL);

-- three upserts: /home, /sql, /home

SELECT page, visits FROM daily_visits ORDER BY page;
Beklenen çıktı
page | visits
/home | 2
/sql | 1

Alıştırma

Depoya mal geldi: 200 Notebook, 50 Pen set ve yeni ürün Tablet (kategori Electronics, fiyat 520) için 10 adet. Tek bir upsert ile var olan ürünlerin stoğunu artır, yenisini ekle (products.name üzerindeki benzersiz indeks başlangıç kodunda var). Sonra bu üç ürünün name ve stock değerlerini ada göre sıralayarak göster.

Alıştırma · SQL
CREATE UNIQUE INDEX idx_products_name ON products (name);

-- one INSERT ... ON CONFLICT here

SELECT name, stock FROM products
WHERE name IN ('Notebook', 'Pen set', 'Tablet')
ORDER BY name;
Beklenen çıktı
name | stock
Notebook | 700
Pen set | 350
Tablet | 10

Unutma

  • Upsert tek ve atomik bir komuttur: INSERT ... ON CONFLICT (anahtar) DO NOTHING | DO UPDATE SET ...; hedef bir benzersizlik kısıtlamasına karşılık gelmelidir.
  • excluded eklenmeye çalışılan satır, tablonun adı ise var olan satırdır; DO UPDATE ... WHERE gereksiz güncellemeleri keser.
  • INSERT OR REPLACE sil + ekle demektir: yeni id, kaybolan sütunlar, öksüz bağlantılar.
  • RETURNING değişen satırları hemen döndürür (SQLite 3.35+, PostgreSQL); SQL Server'da OUTPUT vardır, MySQL'de yoktur.
  • MERGE (PostgreSQL 15+, SQL Server, Oracle) ve MySQL'in ON DUPLICATE KEY UPDATE komutu aynı ihtiyacın başka biçimleridir.
  • İyi bir yükleme idempotenttir: ikinci çalıştırma hiçbir şeyi değiştirmemelidir.
Bu derste bir hata ya da yanlışlık mı gördün?

Kendini test et

12 soru. Her doğru cevap XP kazandırır.

Soru 1 / 12

Soru 1 / 12. ON CONFLICT (name) DO UPDATE SET price = excluded.price komutunda excluded nedir?