loading..
Русский    English
13:51
листать

Оператор MERGE

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

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

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

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

Консоль
Выполнить
  1. SELECT ship, ship class FROM Outcomes O JOIN Classes C ON C.class=O.ship;

ship    class
Bismarck    Bismarck
Tennessee    Tennessee

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

Консоль
Выполнить
  1. SELECT year(MIN(date)) min_year, ship, ship class
  2. FROM outcomes O JOIN battles B ON O.battle= B.name
  3. JOIN Classes C ON C.class=O.ship GROUP BY ship;

min_year    ship    class
1941    Bismarck    Bismarck
1944    Tennessee    Tennessee

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

  1. MERGE Ships AS target  -- таблица, которая будет меняться
  2. USING (SELECT year(MIN(date)), ship, ship
  3.        FROM outcomes O JOIN battles B ON O.battle= B.name
  4.                JOIN Classes C ON C.class=O.ship GROUP BY ship
  5.       ) AS source (min_year,ship, class)  -- источник данных, который мы рассмотрели выше
  6. ON (target.name = source.ship)  -- условие связи между источником и изменяемой таблицей
  7. WHEN MATCHED AND target.launched IS NULL -- если головной корабль есть в таблице Ships
  8.            -- с неизвестным годом
  9.     THEN UPDATE SET target.launched = source.min_year -- обновление
  10. WHEN NOT MATCHED  -- если головного корабля нет в таблице Ships
  11.     THEN INSERT VALUES(source.ship, source.class, source.min_year) -- вставка
  12. OUTPUT $action, inserted.*, deleted.*; -- можно вывести измененные строки

$action    name    class    launched    name    class    launched
INSERT    Bismarck    Bismarck    1941    NULL    NULL    NULL

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

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

Консоль
Выполнить
  1. SELECT * FROM Ships WHERE name='Tennessee';

name    class    launched
Tennessee    Tennessee    1920

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

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

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

  1. WHEN MATCHED THEN DELETE
то удалим корабль Tennessee:

$action    name    class    launched    name    class    launched
INSERT    Bismarck    Bismarck    1941    NULL    NULL    NULL
DELETE    NULL    NULL    NULL    Tennessee    Tennessee    1920

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

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

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

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

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

Результат:

$action    name    class    launched    name    class    launched
DELETE    NULL    NULL    NULL    Iowa    Iowa    1943
DELETE    NULL    NULL    NULL    Kongo    Kongo    1913
DELETE    NULL    NULL    NULL    North Carolina    North Carolina    1941
DELETE    NULL    NULL    NULL    Renown    Renown    1916
DELETE    NULL    NULL    NULL    Revenge    Revenge    1916
DELETE    NULL    NULL    NULL    Yamato    Yamato    1941
INSERT    Bismarck    Bismarck    1941    NULL    NULL    NULL

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

Bookmark and Share
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FOREIGN KEY FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INFORMATION_SCHEMA INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker Больше тэгов
Учебник обновлялся
месяц назад
обмен с wechat . Выращивание хорошего урожая томатов на даче
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.