Orasql.ru

Appearance

Слияние данных. MERGE ​

Команда MERGE позволяет выбрать строки из одного источника и использовать их для обновления, вставки или удаления строк в таблице.

Вся прелесть данной команды в том, что в некоторых она позволяет сделать все эти операции в одном выражении SQL.

Подготовка данных ​

Будем использовать следующие таблицы:

sql
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 уже указаны некоторые сотрудники безопасности, только у них указаны другие аудитории. То есть, чтобы решить задачу, нам нужно сделать несколько действий:

  1. 1. Обновить номера аудиторий у уже существующих записей в таблице ted_speakers
  2. 2. Добавить туда недостающих сотрудников из отдела IT

Данную задачу можно очень просто решить, используя 2 уже известных оператора INSERT и UPDATE:

sql
-- Сначала обновим аудиторию у тех сотрудников, которые уже записаны
-- в список выступающих, и которые работают в отделе безопасности
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:

sql
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')) -- добавляем его

Смотрим на результат:

sql
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 можно использовать и операцию удаления. Но есть одна особенность:

Операция DELETE в MERGE будет производиться только для тех строк, которые были обновлены командой UPDATE

Решим следующую задачу: удалить из списка выступающих всех сотрудников IT подразделения, кроме сотрудника с id = 5; для этого сотрудника нужно сдвинуть дату выступления на 1 месяц вперед.

Напишем следующий запрос:

sql
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, то она ничего не удалит. Следующий код поможет понять, почему:

sql
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 выполняется только для обновленных строк, перепишем запрос:

sql
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

Здесь дата 1 марта для сотрудника с id = 5 была получена в результате последовательного выполнения запросов из учебника. В случае, если бы мы сразу запустили данный запрос, дата была бы на 1 месяц больше, чем было изначально - 1 февраля 2020 года.

Теперь все работает, как нам нужно. Все потому, что в update мы не прописывали никаких условий, т.е он обновил месяц выступления для каждого сотрудника IT подразделения, выступающего на конференции. Далее, команда DELETE была запущена для каждой строки с выступающими из IT отдела. Вот уже в ней мы добавили условие where ts.emp_id <> 5. То есть она удалила всех сотрудников IT отдела за исключением сотрудника с ID = 5.