SQL UPDATE ב-SQLite: WHERE, עדכון כמה עמודות ו-FROM

UPDATE משנה שורות קיימות

INSERT מוסיף שורות חדשות. UPDATE משנה שורות שכבר קיימות. הצורה קצרה ושווה לשנן אותה:

UPDATE table_name
SET column = value
WHERE condition;

דוגמה עובדת:

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 אומר מה משתנה. WHERE אומר אילו שורות. שאר הטבלה לא נפגעת.

פסוקית WHERE היא לא אופציונלית בפועל

מבחינה טכנית, WHERE אופציונלי. מבחינה מעשית, השמטה שלו היא הדרך שבה מהנדסים מתחילים הורסים לעצמם את אחר הצהריים:

UPDATE users SET status = 'inactive';
-- every single user is now inactive

בלי סינון, כל שורה מתאימה. SQLite ייענה בשקט. תמיד כתבו קודם את ה-WHERE, ואז את ה-SET: ההרגל הזה לבדו מונע הרבה תאונות.

אם אתם לא בטוחים שה-WHERE שלכם נכון, הריצו קודם את אותו תנאי כ-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');

-- Preview which rows would be touched.
SELECT id, name, status FROM users WHERE status = 'pending';

-- Then run the update with the exact same WHERE.
UPDATE users SET status = 'active' WHERE status = 'pending';

SELECT * FROM users;

אותו תנאי, שתי פקודות. ה-SELECT הוא ההרצה היבשה שלכם.

עדכון כמה עמודות

הפרידו את ההשמות בפסיקים בתוך SET יחיד. SET אחד, עמודות רבות:

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;

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

ביטויים מימין ל-=

הערך שאחרי = לא חייב להיות ליטרל. הוא יכול להיות כל ביטוי, כולל כזה שמתייחס לערך הנוכחי של העמודה:

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);

-- Raise prices by 10%.
UPDATE products
SET price = price * 1.10;

-- Decrement stock for one row.
UPDATE products
SET stock = stock - 1
WHERE id = 2;

SELECT * FROM products;

price * 1.10 קורא את המחיר הקיים, מכפיל, וכותב את התוצאה בחזרה. SQLite מחשב את הצד הימני לפי הערכים הנוכחיים של השורה לפני שאחת מההשמות של הפקודה הזו נכנסת לתוקף, כך שאפשר להתייחס לכמה עמודות בבטחה:

UPDATE products SET price = price * 1.10, stock = stock + price;
-- 'price' on the right side here is the OLD price, not the just-updated one.

UPDATE ... FROM: שליפת ערכים מטבלה אחרת

מאז SQLite 3.33, UPDATE תומך בפסוקית FROM לעדכונים בין טבלאות. זו הדרך הנקייה ביותר לסנכרן נתונים בין טבלאות:

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;

תת השאילתה מחשבת סכומים לכל לקוח. ה-UPDATE החיצוני מצרף את התוצאות האלה בחזרה ל-customers לפי id. בלי UPDATE ... FROM הייתם כותבים תת שאילתה מתואמת לכל עמודה, וזה הרבה יותר רועש.

כמה כללים שכדאי לזכור:

• טבלת היעד באה אחרי UPDATE, לא ברשימת ה-FROM.
• פסוקית ה-WHERE מבצעת את ה-join: אין כאן מילת מפתח ON.
• אם ה-join יכול להתאים ליותר משורה אחת ב-FROM, התוצאה לא מוגדרת. ודאו שמפתחות ה-join מפיקים לכל היותר התאמה אחת לכל שורת יעד.

RETURNING: לראות מה השתנה

SQLite (3.35 ומעלה) מאפשר ל-UPDATE להחזיר את השורות ששונו באותה פקודה. נוח כשהאפליקציה צריכה את הערכים שאחרי העדכון בלי 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;

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

UPDATE OR REPLACE: טיפול בהתנגשויות אילוצים

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

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');

-- This would fail with a UNIQUE constraint violation:
-- UPDATE users SET email = 'boris@x.com' WHERE id = 1;

-- OR REPLACE deletes the conflicting row, then performs the update.
UPDATE OR REPLACE users
SET email = 'boris@x.com'
WHERE id = 1;

SELECT * FROM users;

האפשרויות הן OR ABORT (ברירת המחדל), OR REPLACE, OR IGNORE, OR FAIL ו-OR ROLLBACK. REPLACE היא המסוכנת: היא מוחקת את השורה המתנגשת, וזה יכול להתגלגל דרך מפתחות זרים. השתמשו בה רק כשאתם באמת מתכוונים ל"אם כבר יש שורה עם הערך הייחודי הזה, תזרוק אותה".

לרוב העבודה בסגנון upsert, התחביר הייעודי INSERT ... ON CONFLICT ברור יותר. יש על זה דף.

עטפו עדכונים מסוכנים בטרנזקציה

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

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;

אם הפקודה השנייה נכשלת (נניח שאילוץ נכנס לפעולה), ROLLBACK מבטל את הראשונה. בלי טרנזקציה, הייתם נשארים עם העברה חצי גמורה: ל-Ada 25 פחות, ו-Boris ללא שינוי. לטרנזקציות יש פרק משלהן בהמשך. בינתיים פשוט דעו שהן קיימות, ושעדכונים גדולים כמעט תמיד שייכים לתוך אחת.

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

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

• שכחת WHERE: מעדכנת כל שורה. קראו את הפקודה בקול לפני שאתם מריצים אותה.
• אופרטור שגוי ב-WHERE: WHERE status = NULL לא מתאים לשום דבר. השתמשו ב-IS NULL. נכסה את זה בדף על אופרטורים.
• עדכון עם תת שאילתה שמחזירה יותר משורה אחת כשאתם מצפים לאחת. השתמשו ב-LIMIT 1 או בצבירה בתת השאילתה, אחרת תקבלו שגיאות או תוצאות מפתיעות.
• בלבול בין UPDATE OR REPLACE ל-UPSERT. OR REPLACE מוחק שורות מתנגשות. INSERT ... ON CONFLICT DO UPDATE משנה אותן במקום. אלה פעולות שונות.

הבא בתור: DELETE

UPDATE משנה שורות. DELETE מסיר אותן. אותה משמעת של WHERE חלה, ואותו הרגל של "הריצו קודם SELECT" יציל אתכם מאותם סוגי אסונות. זה הדף הבא.