Как обновить строки MySQL, связывая события по дате

Решение заключается в том, чтобы для каждого события‑конца найти ближайшее событие‑начало, которое наступает после него, и обновить поле конца ссылкой на ID этого начала. В MySQL это можно выполнить одним оператором UPDATE, используя либо коррелированный подзапрос, либо соединение с производной таблицей, в которой выбирается минимальная дата начала, превышающая дату окончания.

Содержание

• Понимание задачи
• Подход 1: коррелированный подзапрос
• Подход 2: JOIN с derived‑таблицей
• Обработка случаев без подходящего начала
• Полный пример
• Заключение
• Источники

Понимание задачи

У вас есть таблица events:

• criteria = 1 – событие заканчивается.
• criteria = 2 – событие начинается.

Нужно обновить каждую строку‑конец так, чтобы она ссылалась на следующую строку‑начало, которое начинается после её окончания (end_date_time < start_date_time).

Подход 1: коррелированный подзапрос

Классический способ – использовать коррелированный подзапрос, который возвращает id ближайшего начала.

UPDATE events AS e_end
SET e_end.next_start_id = (
    SELECT e_start.id
    FROM events AS e_start
    WHERE e_start.criteria = 2
      AND e_start.date_time > e_end.date_time
    ORDER BY e_start.date_time
    LIMIT 1
)
WHERE e_end.criteria = 1
  AND EXISTS (
      SELECT 1
      FROM events AS e_start
      WHERE e_start.criteria = 2
        AND e_start.date_time > e_end.date_time
      LIMIT 1
  );

Что здесь происходит?

1. e_end – «конец» события.
2. В подзапросе ищем строку‑начало, где date_time больше, чем у e_end.
3. ORDER BY гарантирует, что берём именно ближайшее.
4. LIMIT 1 делает запрос быстрым.
5. Внешний WHERE EXISTS гарантирует, что обновление произойдет только если есть подходящее начало.

Ключевой момент: UPDATE … SET … (SELECT …) работает только в MySQL 8.0+ с поддержкой коррелированных подзапросов в SET – впрочем, это довольно удобно.
Смотрите официальную документацию MySQL о UPDATE с подзапросами: https://dev.mysql.com/doc/refman/8.0/en/update.html.

Подход 2: JOIN с derived‑таблицей

Альтернативный способ – построить derived‑таблицу, содержащую ближайшее начало для каждого конца, а затем выполнить обычный UPDATE … JOIN.

UPDATE events AS e_end
JOIN (
    SELECT e_start.criteria AS crit_start,
           MIN(e_start.date_time) AS next_start_time,
           e_start.id AS next_start_id,
           e_end.id AS end_id
    FROM events AS e_start
    JOIN events AS e_end
        ON e_start.criteria = 2
       AND e_end.criteria = 1
       AND e_start.date_time > e_end.date_time
    GROUP BY e_end.id
) AS d
  ON e_end.id = d.end_id
SET e_end.next_start_id = d.next_start_id
WHERE e_end.criteria = 1;

Пояснение:

1. Внутренний JOIN связывает каждый конец с каждым началом, где дата начала позже даты окончания – это делает запрос более гибким.
2. GROUP BY e_end.id и MIN(e_start.date_time) выбирают ближайшее начало.
3. Внешний UPDATE … JOIN обновляет поле next_start_id в таблице events.

Преимущество: такой подход легко читабелен и может быть более эффективным для больших таблиц, поскольку MySQL может оптимизировать два JOIN.
Подробности о UPDATE … JOIN можно найти в MySQL‑документации: https://dev.mysql.com/doc/refman/8.0/en/update.html#sql-update-join.

Обработка случаев без подходящего начала

В обоих примерах обновление будет пропущено, если для конкретного конца нет будущего начала, в итоге такие записи останутся без ссылки.
Если вы хотите установить NULL или другое значение, можно добавить COALESCE в подзапросе или изменить SET:

SET e_end.next_start_id = COALESCE(
    (SELECT ...), NULL
);

Полный пример

Предположим, у вас есть таблица events:

CREATE TABLE events (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    date_time DATETIME,
    criteria TINYINT,          -- 1 = end, 2 = start
    next_start_id INT NULL     -- поле для ссылки
);

Заполните тестовыми данными:

INSERT INTO events VALUES
(1, 'End A', '2024-01-01 10:00', 1, NULL),
(2, 'Start B', '2024-01-01 11:00', 2, NULL),
(3, 'End C', '2024-01-01 12:00', 1, NULL),
(4, 'Start D', '2024-01-01 13:00', 2, NULL),
(5, 'End E', '2024-01-01 14:30', 1, NULL);

Выполните обновление с коррелированным подзапросом:

UPDATE events AS e_end
SET e_end.next_start_id = (
    SELECT e_start.id
    FROM events AS e_start
    WHERE e_start.criteria = 2
      AND e_start.date_time > e_end.date_time
    ORDER BY e_start.date_time
    LIMIT 1
)
WHERE e_end.criteria = 1;

После выполнения:

Заключение

• Ключевой принцип – для каждого окончания найти ближайшее начало с помощью date_time > e_end.date_time.
• Коррелированный подзапрос проще в написании и работает в MySQL 8.0+.
• JOIN с derived‑таблицей более гибкий для больших наборов данных и может быть легче оптимизировать.
• В обоих подходах важно обрабатывать случаи, когда подходящего начала нет.

Эти техники позволяют полностью автоматизировать связывание событий в одной таблице, без необходимости писать сложные триггеры или внешние скрипты – в общем, просто обновите и всё готово.

Источники

1. Официальная документация MySQL о UPDATE и подзапросах: https://dev.mysql.com/doc/refman/8.0/en/update.html
2. Руководство по UPDATE … JOIN в MySQL: https://dev.mysql.com/doc/refman/8.0/en/update.html#sql-update-join
3. Примеры использования коррелированных подзапросов в UPDATE на Stack Overflow: https://stackoverflow.com/questions/xxxxxx
4. Блог о работе с датами и временем в MySQL: https://www.mysqltutorial.org/mysql-datetime

Важно: всегда создавайте резервную копию таблицы перед массовыми обновлениями и тестируйте запросы в безопасном окружении.