UPDATE, DELETE и MERGE INTO: семантика изменений

INSERT добавляет строки, но данные нужно ещё и менять — править значения, удалять устаревшее, синхронизировать таблицу с новой порцией данных.

Data-modifying CTE: INSERT, UPDATE, DELETE внутри WITH Этот урок про три команды изменения: UPDATE, DELETE и MERGE INTO. Первые две — стандартный SQL, знакомый по любой базе. MERGE INTO — новинка DuckDB 1.4, и это самая мощная из трёх: одна команда, выполняющая вставку, обновление и удаление по условию.

Все три команды работают под защитой MVCC из модуля про транзакции: изменения атомарны, откатываемы и не блокируют читателей. Здесь мы сосредоточимся на их семантике — что именно и при каких условиях они делают.

UPDATE: правка значений

UPDATE меняет значения в существующих строках. Базовая форма:

UPDATE products SET price = price * 1.1 WHERE category = 'electronics';
-- поднять цену на 10% во всех строках категории electronics

UPDATE products SET price = 0, in_stock = false WHERE id = 42;
-- изменить несколько колонок одной строки

WHERE определяет, какие строки затронуть. Без WHERE обновляются все строки таблицы — это частый источник аварий: забытое WHERE переписывает таблицу целиком.

UPDATE умеет брать новые значения из другой таблицы — UPDATE ... FROM:

-- обновить цены products значениями из таблицы price_updates
UPDATE products
SET price = u.new_price
FROM price_updates u
WHERE products.id = u.product_id;

С точки зрения хранилища UPDATE в DuckDB — это, упрощённо, удаление старой версии строки и появление новой; MVCC сохраняет старую версию для отката и для читателей с более ранним снимком. Поэтому массовый UPDATE по стоимости ближе к перезаписи затронутых строк, чем к точечной правке байта на месте.

DELETE: удаление строк

DELETE удаляет строки, удовлетворяющие условию:

DELETE FROM events WHERE event_date < DATE '2024-01-01';
-- удалить события старше 2024 года

DELETE FROM events WHERE user_id IN (SELECT id FROM banned_users);
-- удалить по условию с подзапросом

Как и у UPDATE, DELETE без WHERE удаляет все строки таблицы. Та же осторожность обязательна.

Важное различие на уровне хранилища: DELETE FROM t (всех строк) и TRUNCATE t — не одно и то же. DELETE обрабатывает удаление построчно в рамках транзакции (каждая строка помечается удалённой, MVCC хранит версии). TRUNCATE — оптимизированная операция массовой очистки: она быстро освобождает данные таблицы целиком. Для «удалить вообще всё» TRUNCATE быстрее.

DELETE FROM staging_table;   -- построчное удаление, дороже
TRUNCATE staging_table;      -- быстрая массовая очистка

MERGE INTO: одна команда для слияния

Теперь — главное в уроке. Часто нужна не отдельная вставка или отдельное обновление, а слияние: есть целевая таблица и есть порция новых данных (source); для каждой строки source нужно решить — если совпадение в целевой таблице есть, обновить его; если нет — вставить новую строку. Это типичная задача инкрементального обновления витрины, синхронизации с источником, дозагрузки с обновлениями.

До DuckDB 1.4 это делали через INSERT ... ON CONFLICT или несколькими отдельными командами. В DuckDB 1.4 появился полноценный MERGE INTO — одна декларативная команда для всей логики слияния.

Базовая структура:

MERGE INTO target AS t
USING source AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET value = s.value, updated_at = now()
WHEN NOT MATCHED THEN INSERT (id, value, updated_at)
                       VALUES (s.id, s.value, now());

Читается как описание правил: «слить source в target, сопоставляя по t.id = s.id; для совпавших строк — обновить; для несовпавших строк source — вставить».

MERGE INTO поддерживает несколько типов веток:

• WHEN MATCHED THEN UPDATE SET ... — для совпавших строк обновить значения.
• WHEN MATCHED THEN DELETE — для совпавших строк удалить их из целевой таблицы.
• WHEN NOT MATCHED THEN INSERT ... — для строк source без совпадения вставить новую строку.

Ветки WHEN MATCHED могут иметь дополнительное условие (WHEN MATCHED AND s.value IS NULL THEN DELETE), и тогда одна команда выражает довольно сложную логику слияния: что-то обновить, что-то удалить, остальное вставить — за один проход.

Чем MERGE INTO отличается от INSERT … ON CONFLICT

Это ключевой момент урока, и его нужно понять точно.

INSERT ... ON CONFLICT (а также его краткие формы INSERT OR REPLACE / INSERT OR IGNORE) опирается на ограничение таблицы: конфликт определяется по PRIMARY KEY или UNIQUE. Без такого ограничения механизму не на что опереться — «конфликту» неоткуда взяться.

MERGE INTO устроен иначе. Он не требует первичного ключа и работает по произвольному условию слияния, заданному в ON. Условие ON — это не обязательно равенство по ключу; это любое логическое выражение, по которому сопоставляются строки source и target. MERGE INTO — альтернатива INSERT ... ON CONFLICT, снимающая его главное ограничение.

Практический выбор: если у таблицы есть PRIMARY KEY и нужна простая логика «вставить или заменить» — INSERT OR REPLACE короче и достаточно. Если ключа нет, или условие слияния сложнее равенства по ключу, или нужно в одной команде и обновлять, и удалять, и вставлять — это случай MERGE INTO.

Пример: инкрементальное обновление витрины

Соберём реалистичный сценарий. Есть витрина customer_stats и дневная порция обновлений daily_updates. Нужно: для известных клиентов обновить статистику, для новых — добавить строки.

CREATE TABLE customer_stats (
  customer_id INTEGER,
  total_orders INTEGER,
  last_seen DATE
);
INSERT INTO customer_stats VALUES
  (1, 10, DATE '2026-05-01'),
  (2, 5,  DATE '2026-05-02');

-- дневная порция: клиент 2 известен, клиент 3 новый
CREATE TABLE daily_updates (customer_id INTEGER, new_orders INTEGER, day DATE);
INSERT INTO daily_updates VALUES
  (2, 3, DATE '2026-05-20'),
  (3, 1, DATE '2026-05-20');

MERGE INTO customer_stats AS t
USING daily_updates AS s
ON t.customer_id = s.customer_id
WHEN MATCHED THEN
  UPDATE SET total_orders = t.total_orders + s.new_orders,
             last_seen = s.day
WHEN NOT MATCHED THEN
  INSERT (customer_id, total_orders, last_seen)
  VALUES (s.customer_id, s.new_orders, s.day);

SELECT * FROM customer_stats ORDER BY customer_id;
-- 1  10  2026-05-01    <- не затронут (нет в daily_updates)
-- 2  8   2026-05-20    <- обновлён: 5 + 3 заказа, новая дата
-- 3  1   2026-05-20    <- вставлен как новый клиент

Одна команда обработала и обновление существующего клиента, и вставку нового — ровно та задача, ради которой MERGE INTO и существует. Заметьте: у customer_stats нет первичного ключа, и MERGE INTO это не мешает — он сопоставляет строки по условию ON.

Попробуй сам

1. Создай таблицу acc(id INTEGER, balance INTEGER) с несколькими строками. Выполни UPDATE acc SET balance = balance + 100 WHERE id = 1; и проверь результат. Затем — для безопасности на учебных данных — посмотри, что сделал бы UPDATE без WHERE, через SELECT count(*) FROM acc.
2. Выполни DELETE FROM acc WHERE balance < 0; (если таких строк нет, сначала создай одну). Сравни с TRUNCATE на копии таблицы: чем отличается «удалить все строки через DELETE» от «TRUNCATE»?
3. Создай целевую таблицу target(k INTEGER, v INTEGER) без первичного ключа и source-таблицу src(k INTEGER, v INTEGER), где часть k пересекается с target, часть — новые.
4. Напиши MERGE INTO target USING src ON target.k = src.k с ветками WHEN MATCHED THEN UPDATE и WHEN NOT MATCHED THEN INSERT. Проверь результат: обновились ли совпавшие строки, вставились ли новые? Обрати внимание, что первичного ключа у target нет — и это не помешало.
5. Добавь в MERGE INTO ветку с условием — например, WHEN MATCHED AND src.v IS NULL THEN DELETE. Подготовь данные так, чтобы эта ветка сработала, и убедись, что одна команда выполнила и обновление, и удаление, и вставку.