Новое в стандарте и реализациях языка SQL

Если головной корабль из таблицы Outcomes отсутствует в таблице Ships, добавить его в Ships, приняв имя класса, совпадающим с именем корабля, и год спуска на воду, равным году самого раннего сражения, в котором участвовал корабль. Если же корабль присутствует в Ships, но дата спуска на воду его неизвестна, установить его равным году самого раннего сражения, в котором участвовал корабль.

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

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

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

Консоль

Выполнить

SELECT ship, ship class FROM Outcomes O JOIN Classes C ON C.class=O.ship;

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;

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';

select * from Ships where name='Tennessee';

    Схема

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

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

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

WHEN MATCHED THEN DELETE

Инструкцию 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.