Обновление значения в SQL с помощью JOIN: руководство

Быстрый ответ

Если вам необходимо выполнить команду UPDATE с использованием JOIN в SQL, то вы можете воспользоваться следующим кодом:

UPDATE t1
SET t1.column = t2.column
FROM Table1 t1
JOIN Table2 t2 ON t1.id = t2.foreignKey
WHERE t1.condition;

Здесь происходит объединение данных из таблиц Table1 и Table2 по их реляционным ключам. Использование удобных псевдонимов (t1, t2) улучшает читаемость кода. Условие WHERE определяет строки, которые подлежат обновлению. Это действие эквивалентно выполнению любого типа запроса JOIN.

Подробности работы UPDATE с JOIN

Начнём с общего понимания. Одной из ключевых особенностей SQL является способность соединять данные из различных таблиц. Чтобы изменить строки в одной таблице на основе данных в другой, применяется метод прямого объединения таблиц. SQL Server обеспечивает высокое качество выполнения такых операций.

Типы JOIN в SQL Server

Выбор типа JOIN определяет способ выполнения операций UPDATE:

• INNER JOIN: Обновление строк происходит в тех случаях, когда в обеих таблицах имеются соответствующие записи.
• LEFT JOIN: Обновляет строки только в левой таблице, которые не имеют соответствующих записей. Значения обновляются на NULL или на значения по умолчанию.
• RIGHT JOIN: Работает аналогично LEFT JOIN, но роли правой и левой таблиц меняются местами.
• FULL JOIN: Комбинирует действия LEFT и RIGHT JOIN, обновляя соответствующие строки в обеих таблицах.

Необходимость использования псевдонимов для таблиц

Необходимо уделить внимание использованию псевдонимов таблиц (a, b вместо table1, table2). Это упрощает код, делает его менее запутанным, особенно когда работаете с большим количеством таблиц или когда названия таблиц содержат несколько слов.

Роль оператора WHERE

Оператор WHERE позволяетя определить, какие строки должны быть обновлены. Чёткое и структурированное условие WHERE помогает избежать возможных "шумов", которые могут исказить ваши данные.

Механизм блокировки

Работая с большим объемом данных, следует принимать во внимание особенности работы блокировки в SQL Server. Обычно блокируются отдельные строки, страницы и иногда целые таблицы, что может повлиять на другие одновременные операции. Поэтому протестируйте ваш запрос UPDATE тщательно; при необходимости, подумайте о разделении его на несколько транзакций.

Когда использовать подзапросы

Если вам требуется обновить данные с использованием агрегированных значений, таких как SUM или MAX, то лучше использовать подзапросы. Пример кода:

UPDATE t1
SET t1.aggregateValue = (SELECT MAX(t2.value) FROM Table2 t2 WHERE t2.foreignKey = t1.id)
FROM Table1 t1
WHERE t1.condition;

Визуализация

Возьмите для примера рок-группу 🎸(таблица A), которая настраивает свои инструменты по указаниям звукорежиссера 🎚️ (таблица B):

UPDATE A
SET A.guitar_tune = B.sound_check
FROM RockBand A
JOIN SoundTech B ON A.band_lead = B.tech_lead;

В результате звучание группы становится согласованным 🎵:

До: Band 🎸🎵 ≠ SoundTech 🎚️🎵
После:  Band 🎸🎵 == SoundTech 🎚️🎵

Отсюда и важность использования команды UPDATE в сочетании с JOIN в SQL Server!

Сложные сценарии: NULL, дубликаты, производительность

Обработка столбцов с NULL

Будьте внимательны с NULL, поскольку они могут негативно повлиять на результаты. Если t2.column может принимать значение NULL и вы хотите сохранить текущие значения t1.column, воспользуйтесь проверкой IS NULL:

UPDATE t1
SET t1.column = ISNULL(t2.column, t1.column)
FROM Table1 t1
JOIN Table2 t2 ON t1.id = t2.foreignKey
WHERE t1.condition;

Подробнее об этом расскажет наш спикер на видео

Работа с дублирующимися строками

Какие действия предпринять, если в Table2 есть дублирующиеся ключи? Вы возможно можете обновить данные Table1, используя произвольные совпадающие значения. Однако, используя подзапрос, можно выбрать наиболее подходящие данные:

UPDATE t1
SET t1.column = (
  SELECT TOP 1 t2.column
  FROM Table2 t2
  WHERE t2.foreignKey = t1.id
  ORDER BY t2.criteria DESC
)
FROM Table1 t1
WHERE t1.condition;

Оптимизация для улучшения производительности

При работе с большими объемами данных возможно потребуются специальные приемы. Индексация столбцов, которые используются в условиях JOIN и WHERE, это прекрасный способ улучшить производительность.

Обновления в разных базах данных

При переходе на работу с международными проектами и выполнении операций обновления в разных базах данных вам будут необходимы специальные разрешения доступа и полные имена таблиц:

UPDATE db1.dbo.Table1
SET column = db2.dbo.Table2.column
FROM db1.dbo.Table1 t1
JOIN db2.dbo.Table2 t2 ON t1.id = t2.foreignKey
WHERE t1.condition;

Полезные материалы

1. UPDATE (Transact-SQL) – SQL Server | Microsoft Learn — Официальная документация по UPDATE с упоминанием JOIN.
2. sql server – SQL update query using joins – Stack Overflow — Обсуждение сообщества с примерами.
3. SQL Server: UPDATE Statement — Подробное описание работы с UPDATE и JOIN.
4. – YouTube — Визуальное объяснение для тех, кто предпочитает визуальное обучение.
5. Use Caution with SQL Server's MERGE Statement — Лучшие примеры и рекомендации по использованию запросов JOIN в операциях UPDATE/MERGE.