Слияние данных. MERGE
Команда MERGE
позволяет выбрать строки из одного источника и использовать их для обновления, вставки или удаления строк в таблице.
Вся прелесть данной команды в том, что в некоторых случаях она позволяет сделать все эти операции одним запросом.
Подготовка данных
Будем использовать следующие таблицы:
create table employees(
id number not null,
emp_name varchar2(200 char) not null,
department varchar2(100 char) not null,
position varchar2(100 char) not null
);
create table ted_speakers(
emp_id number not null,
room varchar2(30 char),
conf_date date not null
);
insert into employees
values(1, 'Иван Иванов', 'SALARY', 'MANAGER');
insert into employees
values(2, 'Елена Петрова', 'SALARY', 'CLERK');
insert into employees
values(3, 'Алексей Сидоров', 'IT', 'DEVELOPER');
insert into employees
values(4, 'Михаил Иванов', 'IT', 'DEVELOPER');
insert into employees
values(5, 'Владимир Петров', 'IT', 'QA');
insert into ted_speakers
values(1, '201b', to_date('2020.01.01', 'yyyy.mm.dd'));
insert into ted_speakers
values(3, '101', to_date('2020.01.01', 'yyyy.mm.dd'));
insert into ted_speakers
values(5, '201b', to_date('2020.01.01', 'yyyy.mm.dd'));
create table employees(
id number not null,
emp_name varchar2(200 char) not null,
department varchar2(100 char) not null,
position varchar2(100 char) not null
);
create table ted_speakers(
emp_id number not null,
room varchar2(30 char),
conf_date date not null
);
insert into employees
values(1, 'Иван Иванов', 'SALARY', 'MANAGER');
insert into employees
values(2, 'Елена Петрова', 'SALARY', 'CLERK');
insert into employees
values(3, 'Алексей Сидоров', 'IT', 'DEVELOPER');
insert into employees
values(4, 'Михаил Иванов', 'IT', 'DEVELOPER');
insert into employees
values(5, 'Владимир Петров', 'IT', 'QA');
insert into ted_speakers
values(1, '201b', to_date('2020.01.01', 'yyyy.mm.dd'));
insert into ted_speakers
values(3, '101', to_date('2020.01.01', 'yyyy.mm.dd'));
insert into ted_speakers
values(5, '201b', to_date('2020.01.01', 'yyyy.mm.dd'));
Эти таблицы - списки сотрудников компании и список сотрудников, выступающих на конференции TED.
Использование MERGE
Задача: Как стало известно, все сотрудники из небольшого подразделения IT будут выступать на конференции только в аудитории "809".
В таблице ted_speakers
уже указаны некоторые сотрудники безопасности, только у них проставлены другие аудитории.
То есть, чтобы решить задачу, нам нужно сделать несколько действий:
- Обновить номера аудиторий у уже существующих записей в таблице
ted_speakers
- Добавить туда недостающих сотрудников из отдела IT
Задача легко решается с помощью команд INSERT
и UPDATE
:
-- Сначала обновим аудиторию у тех сотрудников, которые уже записаны
-- в список выступающих, и которые работают в отделе безопасности
update ted_speakers ts
set ts.room = '809'
where ts.emp_id in (select emp_id
from employees
where department = 'IT'
);
/* Затем добавим в список выступающих
недостающих сотрудников из IT подразделения
*/
insert into ted_speakers(emp_id, room, conf_date)
select id, '809', to_date('2020.01.03', 'yyyy.mm.dd')
from employees
-- нужны сотрудники из IT подразделения
where department = 'IT'
/*
которых еще нет в таблице выступающих
Для наших небольших таблиц подойдет
полная выборка сотрудников из ted_speakers
*/
and id not in (
select emp_id
from ted_speakers);
-- Сначала обновим аудиторию у тех сотрудников, которые уже записаны
-- в список выступающих, и которые работают в отделе безопасности
update ted_speakers ts
set ts.room = '809'
where ts.emp_id in (select emp_id
from employees
where department = 'IT'
);
/* Затем добавим в список выступающих
недостающих сотрудников из IT подразделения
*/
insert into ted_speakers(emp_id, room, conf_date)
select id, '809', to_date('2020.01.03', 'yyyy.mm.dd')
from employees
-- нужны сотрудники из IT подразделения
where department = 'IT'
/*
которых еще нет в таблице выступающих
Для наших небольших таблиц подойдет
полная выборка сотрудников из ted_speakers
*/
and id not in (
select emp_id
from ted_speakers);
А вот как эту же задачу можно решить с помощью MERGE
:
merge into ted_speakers ts -- (1)
using (
select id
from employees
where department = 'IT') eit -- (2)
on (eit.id = ts.emp_id) -- (3)
when matched then -- (4) Если есть такой сотрудник
update set ts.room = '809' -- обновляем аудиторию
when not matched then -- (5) Если нет такого сотрудника
insert (emp_id, room, conf_date)
values(eit.id, '809', to_date('2020.04.03', 'yyyy.mm.dd')) -- добавляем его
merge into ted_speakers ts -- (1)
using (
select id
from employees
where department = 'IT') eit -- (2)
on (eit.id = ts.emp_id) -- (3)
when matched then -- (4) Если есть такой сотрудник
update set ts.room = '809' -- обновляем аудиторию
when not matched then -- (5) Если нет такого сотрудника
insert (emp_id, room, conf_date)
values(eit.id, '809', to_date('2020.04.03', 'yyyy.mm.dd')) -- добавляем его
Смотрим на результат:
select ts.emp_id, ts.room, emp.emp_name, emp.department
from ted_speakers ts
join employees emp on emp.id = ts.emp_id
select ts.emp_id, ts.room, emp.emp_name, emp.department
from ted_speakers ts
join employees emp on emp.id = ts.emp_id
Все сотрудники из IT отдела были добавлены, у всех аудитория равна 809.
Давайте подробнее разберем, как этот запрос работает.
Для начала, в строке "(1)" мы указываем, в какую таблицу мы будем производить слияние (кстати, слово "merge" с английского так и переводится).
После этого, мы пишем обычный запрос, который будет являться источником данных для наших действий, и указываем его в USING
. Этому подзапросу нужно дать псевдоним, чтобы можно было в дальнейшем обращаться к данным, которые он возвращает(строка "(2)"). В нашем случае источником данных является список всех сотрудников из подразделения IT.
После определения источника данных мы указываем условие, по которому таблица слияния будет с ним соединяться( строка "(3)"). Мы соединяемся по id сотрудника.
Далее мы указываем, что будем делать, если в исходной таблице есть строки, для которых условие "(3)" выполняется, и что делать, если это условие не выполняется. Это происходит в частях запроса "(4)" и "(5)" соответственно.
Теперь мы вместо двух разных команд SQL использовали всего одну. Также, мы сократили количество обращений к БД с двух до одного.
Использование DELETE в MERGE
Кроме операций вставки и обновления в MERGE
можно использовать и операцию удаления. Но есть одна особенность:
INFO
Операция DELETE
в MERGE-запросе будет производиться только для тех строк, которые были обновлены командой UPDATE
Решим следующую задачу: удалить из списка выступающих всех сотрудников IT подразделения, кроме сотрудника с id = 5
; для этого сотрудника нужно сдвинуть дату выступления на 1 месяц вперед.
Напишем запрос:
merge into ted_speakers ts
using (
select id
from employees
where department = 'IT') eit
on (ts.emp_id = eit.id)
when matched then
-- изменим дату выступления для сотрудника с id = 5
update set ts.conf_date = add_months(ts.conf_date, 1)
where ts.emp_id = 5
-- всех остальных сотрудников из it отдела удалим из ted_speakers
delete
where ts.emp_id <> 5
select ts.emp_id, ts.conf_date, emp.emp_name, emp.department
from ted_speakers ts
join employees emp on emp.id = ts.emp_id
merge into ted_speakers ts
using (
select id
from employees
where department = 'IT') eit
on (ts.emp_id = eit.id)
when matched then
-- изменим дату выступления для сотрудника с id = 5
update set ts.conf_date = add_months(ts.conf_date, 1)
where ts.emp_id = 5
-- всех остальных сотрудников из it отдела удалим из ted_speakers
delete
where ts.emp_id <> 5
select ts.emp_id, ts.conf_date, emp.emp_name, emp.department
from ted_speakers ts
join employees emp on emp.id = ts.emp_id
Как видно, дата выступления для сотрудника с id = 5
была изменена, но остальные сотрудники из it подразделения остались в таблице.
Это произошло потому, что в команде UPDATE
мы написали условие where ts.emp_id = 5
.
Это значит, что команда delete
будет выполняться только для одной этой строки. А так как в условии удаления мы написали условие where ts.emp_id <> 5
, то она ничего не удалит.
Следующий код поможет понять, почему:
delete from ted_speakers
where emp_id = 5 and emp_id <> 5 -- это условие всегда ложно!
delete from ted_speakers
where emp_id = 5 and emp_id <> 5 -- это условие всегда ложно!
Зная, что delete
выполняется только для обновленных строк, перепишем запрос:
merge into ted_speakers ts
using (
select id
from employees
where department = 'IT') eit
on (ts.emp_id = eit.id)
when matched then
update set ts.conf_date = add_months(ts.conf_date, 1)
delete
where ts.emp_id <> 5
merge into ted_speakers ts
using (
select id
from employees
where department = 'IT') eit
on (ts.emp_id = eit.id)
when matched then
update set ts.conf_date = add_months(ts.conf_date, 1)
delete
where ts.emp_id <> 5
INFO
Здесь дата 1 марта для сотрудника с id = 5 была получена в результате последовательного выполнения запросов из учебника. В случае, если бы мы сразу запустили данный запрос, дата была бы на 1 месяц больше, чем было изначально - 1 февраля 2020 года.
Теперь все работает, как нам нужно. Все потому, что в update
мы не прописывали никаких условий, т.е он обновил месяц выступления для каждого сотрудника IT подразделения, выступающего на конференции.
Далее, команда DELETE
была запущена для каждой строки с выступающими из IT отдела. Вот уже в ней мы добавили условие where ts.emp_id <> 5
. То есть она удалила всех сотрудников IT отдела за исключением сотрудника с id = 5
.