Оператор MERGE

Эта задача подразумевает выполнение двух разных операторов (INSERT и UPDATE - так называемый UPSERT) на одной таблице (Ships) в зависимости от наличия/отсутствия связанных записей в другой таблице (Outcomes).

Для решения подобных задач стандарт предоставляет оператор MERGE. Рассмотрим его использование на примере решения данной задачи в SQL Server.

Для начала напишем запрос, который вернет нам головные корабли из таблицы Outcomes, т.е. корабли, у которых имя класса совпадает с именем корабля:

select ship, ship class 
from Outcomes O 
    join Classes C on C.class=O.ship;

Теперь добавим соединение с таблицей Battles и выполним группировку, чтобы найти минимальный год сражений каждого такого корабля:

SELECT year(min(date)) min_year, ship, ship class
from outcomes O 
    join battles B on O.battle= B.name
    join Classes C on C.class=O.ship group by ship;

Исходные данные готовы. Теперь мы можем перейти к написанию оператора MERGE.

MERGE Ships AS target -- таблица, которая будет меняться
USING (SELECT year(min(date)), ship, ship
       from outcomes O 
           join battles B on O.battle= B.name
           join Classes C on C.class=O.ship 
       group by ship
      ) AS source (min_year,ship, class) -- источник данных, который мы рассмотрели выше
ON (target.name = source.ship) -- условие связи между источником и изменяемой таблицей
WHEN MATCHED AND target.launched IS NULL -- если головной корабль есть в таблице Ships
                                         -- с неизвестным годом
THEN UPDATE SET target.launched = source.min_year -- обновление
WHEN NOT MATCHED -- если головного корабля нет в таблице Ships
THEN INSERT VALUES(source.ship, source.class, source.min_year) -- вставка
OUTPUT $action, inserted.*, deleted.*; -- можно вывести измененные строки

Предложение OUTPUT позволяет вывести измененные строки. Автоматически создаваемые рабочие таблицы inserted и deleted имеют тот же смысл, что и при использовании в триггерах, т.е. inserted содержит строки, которые были добавлены в изменяемую таблицу, а deleted - удаленные из нее строки.

Поскольку удаления в нашем запросе не было, то соответствующие столбцы имеют значения NULL. Столбец $action содержит название выполненной операции. В нашем случае была выполнена только вставка, поскольку корабль Tennessee содержится в таблице Ships с известным годом спуска на воду:

select * 
from Ships 
where name='Tennessee';

Инструкция MERGE может иметь не больше двух предложений WHEN MATCHED.

Если указаны два предложения, то первое предложение должно сопровождаться дополнительным условием (что имеет место в нашем случае - AND target.launched IS NULL). Для любой строки второе предложение WHEN MATCHED применяется только в том случае, если не применяется первое.

Если имеются два предложения WHEN MATCHED, одно должно указывать действие UPDATE, а другое — DELETE. Т.е. если мы добавим в оператор предложение

WHEN MATCHED THEN DELETE

то удалим корабль Tennessee:

Инструкцию MERGE нельзя использовать для обновления одной строки более одного раза, а также для обновления и удаления одной и той же строки.

Предложение WHEN NOT MATCHED [BY TARGET] THEN INSERT используется для вставки строк из источника, не совпадающих со строками в изменяемой таблице согласно условию связи. В нашем примере такой строкой является строка, относящаяся к кораблю Bismarck. Инструкция MERGE может иметь только одно предложение WHEN NOT MATCHED.

Наконец, оператор MERGE может включать предложение WHEN NOT MATCHED BY SOURCE THEN.

Оно воздействует на те строки изменяемой таблицы, для которых нет соответствия в таблице-источнике. Например, если бы мы хотели удалить из таблицы Ships головные корабли, не принимавшие участие в сражениях, то добавили бы следующее предложение:

WHEN NOT MATCHED BY SOURCE AND target.name=target.class THEN DELETE

Результат:

При помощи этого предложения можно удалять или обновлять строки. Инструкция MERGE может иметь не более двух предложений WHEN NOT MATCHED BY SOURCE. Если указаны два предложения, то первое предложение должно иметь дополнительное условие (как в нашем примере). Для любой выбранной строки второе предложение WHEN NOT MATCHED BY SOURCE применяется только в тех случаях, если не применяется первое. Кроме того, если имеется два предложения WHEN NOT MATCHED BY SOURCE, то одно должно выполнять UPDATE, а другое — DELETE.