Автор Тема: Вставка или обновление данных в одном запросе - UPSERT (mysql, oracle)  (Прочитано 13992 раз)

Марахтанов Алексей

  • Администратор
  • Постоялец
  • *****
  • Сообщений: 205
    • marahtanov.ru
Иногда при разработке информационных систем требуется либо вставить, либо обновить запись в зависимости от ряда условий.

Например, имеются таблицы books (id, title) - список книг, users (id, fio) - пользователи и see(book_id, user_id, cnt) - число просмотров одной книги конкретным пользователем.
Если пользователь с id=2 еще ни разу не просматривал книгу с id=1, то необходимо добавить строку в таблицу see. Если уже просматривал ранее - обновить значение cnt (увеличить на 1).

Решая задачу в лоб, можно сделать это через два запроса: в первом определить, есть ли такая запись в таблице see:
select count(*) from see where book_id=1 and user_id=2и если запрос вернет 0 - выполнить insert, если 1 - update.

Вместо этого в СУБД MySQL можно воспользоваться конструкцией ON DUPLICATE KEY UPDATE, которая позволит выполнить обозначенный функционал в одном запросе (тем самым сократив код и время его написания, упростив логику, сократив время выполнения запроса). Подобные запросы часто называют UPSERT-запросы (UPdate+inSERT).

В нашем случае конструкция ON DUPLICATE KEY UPDATE пример вид:
insert into see (book_id, user_id, cnt) values (1,2,1) ON DUPLICATE KEY UPDATE cnt=cnt+1
Запрос будет обработан следующим образом: если запись найдена, выполнятся условия после ON DUPLICATE KEY UPDATE (их может быть сразу несколько, синтаксис - как и в любом update запросе, только без секции where). Если не найдена - выполнится insert-запрос из первой части (до ON DUPLICATE KEY UPDATE).

Обратите внимание, чтобы данная конструкция сработала, поля, определяющие уникальность строки (в нашем случае поля book_id и user_id) должны быть объявлены как первичные ключи.

Кроме конструкции ON DUPLICATE KEY UPDATE в mysql существует более простой способ выполнить либо вставку, либо обновление в одном запросе.
Это запрос replace, который по синтаксису 1 в 1 соответствует запросу insert, только если в таблице уже существует запись с первичным ключом, переданным в запросе, то эта запись будет удалена и новая запись будет вставлена вместо нее. В ряде случаев это может быть полезно, но обозначенную в примере задачу решить таким способом будет нельзя, поскольку replace удалит старое значение cnt при вставке.

Как сделать то же самое в Oracle? C помощью конструкции merge


merge into see using dual on (book_id = 1 and user_id=2)
when not matched then insert (book_id,user_id,cnt) values (1,2,1)
when matched then update set cnt = cnt+1;
« Последнее редактирование: 05 Ноябрь 2013, 02:17:19 от Марахтанов Алексей »