SQL Ready | Базы Данных on Telegram: “UPDATE ... FROM в PostgreSQL: как обновлять данн…”

UPDATE ... FROM в PostgreSQL: как обновлять данные из другой таблицы и не поймать скрытые дубли!

Задача, которая встречается постоянно: обновить таблицу по данным из другой. Например, проставить клиентам дату последнего заказа.

Есть таблицы:

customers(id, last_order_at)
orders(id, customer_id, created_at)

Интуитивный вариант:

UPDATE customers c
SET last_order_at = o.created_at
FROM orders o
WHERE o.customer_id = c.id;

Запрос выполнится, но если у клиента несколько заказов — возникает вопрос: какая именно дата попадёт в last_order_at? Ответ — это не гарантируется.

Запрос корректен синтаксически, но логически небезопасен. Если JOIN даёт несколько строк для одной записи в customers, PostgreSQL не гарантирует, какую строку он использует при обновлении.

Поэтому сначала нужно свести соответствие к одной строке на клиента. Через агрегат:

UPDATE customers c
SET last_order_at = t.max_created_at
FROM (
    SELECT
        customer_id,
        MAX(created_at) AS max_created_at
    FROM orders
    GROUP BY customer_id
) t
WHERE t.customer_id = c.id;

Теперь для каждого клиента ровно одна строка — обновление становится детерминированным.

Вариант через DISTINCT ON:

UPDATE customers c
SET last_order_at = t.created_at
FROM (
    SELECT DISTINCT ON (customer_id)
        customer_id,
        created_at
    FROM orders
    ORDER BY customer_id, created_at DESC
) t
WHERE t.customer_id = c.id;

Здесь тоже выбирается одна строка на клиента за счёт сортировки. Если возможны одинаковые created_at, лучше добавить tie-breaker:

ORDER BY customer_id, created_at DESC, id DESC

Коррелированный подзапрос:

UPDATE customers c
SET last_order_at = (
    SELECT MAX(o.created_at)
    FROM orders o
    WHERE o.customer_id = c.id
);

Плюс: гарантированно одно значение. Нюанс: обновятся все строки в customers, и у клиентов без заказов будет NULL.

Пример ловушки:

UPDATE customers c
SET last_order_at = t.created_at
FROM (
    SELECT customer_id, created_at
    FROM orders
) t
WHERE t.customer_id = c.id;

С виду безопасно, по факту — та же проблема: соответствие не уникально.

Практическая проверка: замените UPDATE на SELECT с тем же JOIN и посмотрите, есть ли дубли:

SELECT c.id, t.*
FROM customers c
JOIN ...

Если дубли есть — такой UPDATE уже небезопасен. Про индексы:

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);

Помогает и для MAX, и для ORDER BY. Итог: UPDATE ... FROM — крутой инструмент, но он не проверяет однозначность соответствия.

🔥 Если JOIN возвращает несколько строк на одну обновляемую запись, результат не гарантируется. Сначала фиксируем одну строку на ключ — потом обновляем.

➡️ SQL Ready | #практика