UPSERT ב-SQLite: ON CONFLICT DO UPDATE ו-DO NOTHING

הכנסה, או עדכון אם השורה כבר קיימת

צורך נפוץ: להכניס שורה, אבל אם כבר קיימת שורה עם אותו מפתח, לעדכן אותה במקום. בלי UPSERT, הייתם כותבים קודם SELECT, ואז מתפצלים ל-INSERT או ל-UPDATE: שתי גישות למסד הנתונים ותנאי מרוץ ביניהן.

ה-UPSERT של SQLite עושה את זה בפקודה אחת:

CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products (sku, name, price)
VALUES ('A-100', 'Widget', 9.99)
ON CONFLICT(sku) DO UPDATE SET
    name  = excluded.name,
    price = excluded.price;

SELECT * FROM products;

בפעם הראשונה שמריצים את זה, השורה מוכנסת. הריצו שוב עם מחיר אחר ואותו sku, והשורה הקיימת מתעדכנת במקום. בלי כפילות, בלי שגיאה.

האנטומיה של ON CONFLICT

הצורה המלאה:

INSERT INTO table (...) VALUES (...)
ON CONFLICT(conflict_target) DO UPDATE SET col = expr, ...
WHERE condition;

שלושה חלקים חשובים:

• conflict_target: העמודה או העמודות עם אילוץ UNIQUE או PRIMARY KEY שאתם מצפים להתנגשות בהן. SQLite משתמש בזה כדי לבחור באיזה אינדקס לעקוב.
• DO UPDATE SET ...: מה לשנות בשורה הקיימת כשקורית התנגשות. (או DO NOTHING כדי לדלג בשקט.)
• WHERE אופציונלי: תנאי נוסף שחייב להתקיים כדי שהעדכון באמת ירוץ.

יעד ההתנגשות חייב להתאים לאילוץ ייחודי אמיתי. ON CONFLICT(price) לא יעבור הידור אם price לא ייחודית: ל-SQLite אין מול מה לזהות התנגשות.

DO NOTHING: להכניס אם חסר, אחרת לדלג

הגרסה הפשוטה יותר. שימושית כשזורעים נתונים או רושמים אירועים, וכפילויות צריכות פשוט להיות מוזנחות בשקט:

CREATE TABLE seen_events (
    event_id TEXT PRIMARY KEY,
    seen_at  TEXT DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO seen_events (event_id) VALUES ('evt-1')
ON CONFLICT(event_id) DO NOTHING;

INSERT INTO seen_events (event_id) VALUES ('evt-1')
ON CONFLICT(event_id) DO NOTHING;

INSERT INTO seen_events (event_id) VALUES ('evt-2')
ON CONFLICT(event_id) DO NOTHING;

SELECT * FROM seen_events;

ההכנסה השנייה נתקלת באותו event_id ובדרך כלל הייתה מעלה UNIQUE constraint failed. עם DO NOTHING, SQLite פשוט מדלג עליה. בלי חריגה, בלי שורה מושפעת.

זו "ההכנסה האידמפוטנטית" שאנשים משתמשים בשבילה לעיתים קרובות ב-INSERT OR IGNORE. ה-DO NOTHING של UPSERT עושה את אותה עבודה ומשתלב טוב יותר עם פסוקיות WHERE ו-RETURNING.

פסאודו הטבלה excluded

כשהתנגשות מתרחשת, פתאום יש שתי שורות במשחק: הקיימת בטבלה, והחדשה שניסיתם להכניס. SQLite נותן דרך לדבר על שתיהן.

• שמות עמודות רגילים (price, name) מתייחסים לשורה הקיימת.
• excluded.column מתייחס לשורה הנכנסת שנדחתה.

CREATE TABLE inventory (
    sku      TEXT PRIMARY KEY,
    quantity INTEGER NOT NULL,
    updated  TEXT
);

INSERT INTO inventory (sku, quantity, updated)
VALUES ('A-100', 5, '2026-04-01')
ON CONFLICT(sku) DO UPDATE SET
    quantity = quantity + excluded.quantity,
    updated  = excluded.updated;

INSERT INTO inventory (sku, quantity, updated)
VALUES ('A-100', 3, '2026-04-02')
ON CONFLICT(sku) DO UPDATE SET
    quantity = quantity + excluded.quantity,
    updated  = excluded.updated;

SELECT * FROM inventory;

quantity = quantity + excluded.quantity נקרא "הכמות הקיימת ועוד החדשה". אחרי שתי הכנסות, ל-A-100 יש כמות 8. התבנית הזו, צבירה לתוך שורה קיימת, היא אחד הטריקים השימושיים ביותר של UPSERT.

UPSERT מותנה עם WHERE

ה-WHERE שבסוף מאפשר לדלג על העדכון אלא אם תנאי כלשהו מתקיים. הוא רץ מול השורה הקיימת (ויכול להתייחס ל-excluded.* עבור הנכנסת):

CREATE TABLE prices (
    sku        TEXT PRIMARY KEY,
    price      REAL NOT NULL,
    updated_at TEXT NOT NULL
);

INSERT INTO prices VALUES ('A-100', 10.00, '2026-04-01');

INSERT INTO prices (sku, price, updated_at)
VALUES ('A-100', 12.00, '2026-03-15')
ON CONFLICT(sku) DO UPDATE SET
    price      = excluded.price,
    updated_at = excluded.updated_at
WHERE excluded.updated_at > prices.updated_at;

SELECT * FROM prices;

השורה החדשה נושאת updated_at ישן יותר, ולכן ה-WHERE שקרי והעדכון מדולג. השורה הקיימת שומרת על המחיר החדש יותר שלה. החליפו את התאריכים והעדכון ירוץ. זו התבנית הסטנדרטית של "דרוס רק בנתונים עדכניים יותר".

Upsert של כמה שורות

VALUES יכול להכיל שורות רבות, ו-ON CONFLICT חל על כל אחת בנפרד:

CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL NOT NULL
);

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

INSERT INTO products (sku, name, price) VALUES
    ('A-100', 'Widget v2', 10.99),
    ('A-200', 'Gadget',     14.99),
    ('A-300', 'Gizmo',      19.99)
ON CONFLICT(sku) DO UPDATE SET
    name  = excluded.name,
    price = excluded.price;

SELECT * FROM products ORDER BY sku;

A-100 מתנגש ומתעדכן. A-200 ו-A-300 חדשים ומוכנסים. פקודה אחת, תוצאה מעורבת של הכנסה ועדכון. זו דרך נקייה לסנכרן אצווה של רשומות ממקור חיצוני.

UPSERT מול INSERT OR REPLACE

INSERT OR REPLACE נראה כאילו הוא עושה את אותו הדבר. הוא לא.

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT,
    notes TEXT
);

INSERT INTO users VALUES (1, 'Ada', 'ada@example.com', 'VIP customer');

-- Replace: deletes the old row, inserts a new one.
INSERT OR REPLACE INTO users (id, name, email)
VALUES (1, 'Ada Lovelace', 'ada@example.com');

SELECT * FROM users;

notes נעלמה. INSERT OR REPLACE מחק את שורה 1 לגמרי והכניס שורה חדשה: כל עמודה שלא ציינתם אופסה ל-NULL או לברירת המחדל שלה. הוא גם מפעיל טריגרים של DELETE ומתגלגל דרך מפתחות זרים עם ON DELETE.

UPSERT שומר על השורה:

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT,
    notes TEXT
);

INSERT INTO users VALUES (1, 'Ada', 'ada@example.com', 'VIP customer');

INSERT INTO users (id, name, email)
VALUES (1, 'Ada Lovelace', 'ada@example.com')
ON CONFLICT(id) DO UPDATE SET
    name  = excluded.name,
    email = excluded.email;

SELECT * FROM users;

notes עדיין שם. רק העמודות שצוינו ב-SET השתנו. פנו ל-UPSERT כברירת מחדל, ול-INSERT OR REPLACE רק כשאתם באמת רוצים סמנטיקה של מחיקה והכנסה מחדש.

כמה יעדי התנגשות

אם שורה עלולה להתנגש ביותר מאילוץ אחד, אפשר לשרשר פסוקיות ON CONFLICT:

CREATE TABLE accounts (
    id       INTEGER PRIMARY KEY,
    username TEXT UNIQUE,
    email    TEXT UNIQUE,
    score    INTEGER DEFAULT 0
);

INSERT INTO accounts (username, email, score)
VALUES ('rosa', 'rosa@example.com', 10)
ON CONFLICT(username) DO UPDATE SET score = score + excluded.score
ON CONFLICT(email)    DO UPDATE SET score = score + excluded.score;

INSERT INTO accounts (username, email, score)
VALUES ('rosa', 'rosa@example.com', 5)
ON CONFLICT(username) DO UPDATE SET score = score + excluded.score
ON CONFLICT(email)    DO UPDATE SET score = score + excluded.score;

SELECT * FROM accounts;

האילוץ שמופעל ראשון מנצח, וה-DO UPDATE של הענף הזה רץ. בפועל, לרוב הטבלאות יש יעד התנגשות ברור אחד, המפתח הראשי או עמודה ייחודית אחת, ולעיתים רחוקות תצטרכו יותר מפסוקית אחת.

מלכודות נפוצות

כמה דברים שנושכים אנשים:

• אין אינדקס ייחודי תואם, אין UPSERT. ON CONFLICT(col) דורש ש-col תהיה PRIMARY KEY או שיהיה לה אילוץ UNIQUE. אחרת SQLite נכשל עם "no such constraint".
• DO UPDATE לא מופעל אם אין התנגשות. הוא חלופה להכנסה, לא התנהגות נוספת. בפעם הראשונה שמפתח נראה, רק ההכנסה רצה.
• excluded היא לקריאה בלבד. אפשר לקרוא ממנה אבל לא לכתוב אליה. היעד של SET הוא תמיד השורה הקיימת.
• rowid שנוצרים אוטומטית ב-INTEGER PRIMARY KEY. אם לא מספקים את ה-id, כל הכנסה מקבלת id חדש, ואין עם מה להתנגש. UPSERT הגיוני רק כשהעמודה המתנגשת מקבלת ערך דטרמיניסטי מהקוד הקורא.

הבא בתור: RETURNING

UPSERT לא אומר לכם כלום על אילו שורות הוכנסו ואילו עודכנו, או איך נראים הערכים הסופיים שלהן. בשביל זה צריך את פסוקית RETURNING: היא מחזירה את השורות המושפעות באותה פקודה, בלי צורך ב-SELECT נוסף. זה הבא.