UPSERT VALUES

Содержание раздела
  1. Синтаксис
    1. Ключевое слово VALUES
    2. Ключевое слово RETRY
  2. Ограничения
    1. Ограничения выполнения
    2. Ограничения СУБД
    3. Ограничения сущностей
    4. Ограничения ключевых слов
    5. Ограничения партиционирования
  3. Примеры
    1. Вставка данных во все столбцы логической таблицы
    2. Вставка данных в указанные столбцы логической таблицы
    3. Вставка данных в логическую таблицы вне дельты
    4. Вставка данных во все столбцы standalone-таблицы
    5. Вставка данных в указанные столбцы standalone-таблицы
    6. Вставка данных в указанные столбцы партиционированной таблицы
    7. Вставка данных в указанные столбцы партиции
    8. Перезапуск операции по вставке записей

Поддерживается в версиях:  6.4 / 6.3 / 6.2 / 6.1 / 6.0 / 5.8 / 5.7 / 5.6 / 5.5 / 5.4.

Запрос вставляет записи в логической таблицу или standalone-таблицу. Запрос предназначен для обновления записей, но также может использоваться для добавления новых записей.

Запрос поддерживается для СУБД ADB и ADP.

В отличие от INSERT VALUES, запрос UPSERT VALUES обновляет существующую запись только теми значениями, которые указаны в запросе.
Чтобы полностью обновить существующие записи, используйте INSERT VALUES. Чтобы обновить большой объем данных, используйте загрузку данных.

Вставка записей в логические и материализованные представления недоступна.

Синтаксис вставки в standalone-таблицу подразумевает использование внешней writable-таблицы, которая указывает на standalone-таблицу. При вставке данных в standalone-таблицу учитывайте ограничения таблиц в конкретной СУБД.

Запрос обрабатывается в порядке, описанном в разделе Порядок обработки запросов на обновление данных.

В ответе возвращается:

  • объект ResultSet c одной записью, содержащей один столбец sysCn, при успешном выполнении запроса;
  • исключение при неуспешном выполнении запроса.

При вставке данных в логическую таблицу в столбце sysCn возвращается номер выполненной операции записи, при вставке во внешнюю writable-таблицу возвращается пустой столбец sysCn.

Записи вставляются в логическую таблицу следующим образом: если в текущей версии данных таблицы есть запись со значением первичного ключа, указанным в запросе, запись обновляется; иначе добавляется новая запись. Все поля, указанные в запросе, заполняются значениями из запроса, а пропущенные поля остаются без изменений (при обновлении существующей записи) или заполняются значениями по умолчанию (при добавлении новой записи).

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

Подробнее о партиционировании см. в разделе Партиционирование данных.

Незавершенную операцию по вставке данных можно перезапустить, повторив исходный запрос с ключевым словом RETRY. Подробнее о способах обработки незавершенных операций см. в разделе Управление операциями записи.

По умолчанию система ведет статистику обработки запросов к данным. Запросы UPSERT VALUES учитываются в категории WRITE. Чтобы получить статистику, выполните GET_ENTITY_STATISTICS.

Синтаксис

Вставка данных во все столбцы таблицы:

UPSERT INTO [db_name.]table_name VALUES (value_list_1), (value_list_2), ...

Вставка данных в некоторые столбцы таблицы (с сохранением значений остальных столбцов без изменений):

UPSERT INTO [db_name.]table_name (column_list) VALUES (value_list_1), (value_list_2), ...

Перезапуск операции по вставке данных:

RETRY UPSERT INTO [db_name.]table_name [(column_list)] VALUES (value_list_1), (value_list_2), ...

Параметры:

db_name

Имя логической базы данных. Опционально, если выбрана логическая БД, используемая по умолчанию.

table_name

Имя таблицы, в которую вставляются данные. Возможные значения:

column_list

Список имен столбцов указанной таблицы. Имена перечисляются в круглых скобках через запятую. Список опционален, если количество и порядок вставляемых значений (в списке value_list_N) соответствуют количеству и порядку столбцов в таблице.

value_list_N

Список вставляемых значений. Значения указываются в круглых скобках через запятую. Каждый такой список — это строка, вставляемая в таблицу.

Ключевое слово VALUES

Ключевое слово VALUES задает список вставляемых значений. См. также параметр value_list_N.

Ключевое слово RETRY

Ключевое слово перезапускает обработку незавершенной операции записи, созданной запросом UPSERT VALUES. Если ключевое слово не указано, система создает новую операцию и обрабатывает ее.

Пример запроса с ключевым словом RETRY см. ниже. Список незавершенных операций можно получить с помощью запроса GET_WRITE_OPERATIONS.

Перезапуск с ключевым словом RETRY доступен только для операций со статусом «Выполняется». Чтобы перезапустить операцию со статусом «Отменяется», выполните RESUME_WRITE_OPERATION. Подробнее о способах перезапуска и отмены операций см. в разделе Управление операциями записи.

Ключевое слово RETRY недоступно в запросах к standalone-таблицам.

Ограничения

Ограничения выполнения

  • Выполнение запроса недоступно, если есть незавершенная операция по изменению схемы.
  • Не допускается параллельное выполнение идентичных запросов.

Ограничения СУБД

  • Запрос не поддерживает вставку данных в СУБД ADQM и ADG.

Ограничения сущностей

  • Вставка данных в логические и материализованные представления недоступна.

Ограничения ключевых слов

  • Ключевое слово RETRY недоступно в запросах к standalone-таблицам.

Ограничения партиционирования

  • При вставке данных в партиционированную таблицу все записи, для которых нет подходящей партиции, игнорируются.

Примеры

Вставка данных во все столбцы логической таблицы 

-- выбор логической базы данных marketing_new в качестве базы данных по умолчанию
USE marketing_new;

-- открытие новой (горячей) дельты
BEGIN DELTA;

-- вставка трех записей в логическую таблицу sales (данные таблицы расположены в датасорсе adp)
UPSERT INTO sales 
VALUES (200011, '2021-08-21 23:34:10', 'ABC0001', 2, 123, 'Покупка по акции "1+1"'), 
       (200012, '2021-08-22 10:05:56', 'ABC0001', 1, 234, 'Покупка без акций'), 
       (200013, '2021-08-22 13:17:47', 'ABC0002', 4, 123, 'Покупка по акции "Лето"');

-- закрытие дельты
COMMIT DELTA;

Вставка данных в указанные столбцы логической таблицы 

-- выбор логической базы данных marketing_new в качестве базы данных по умолчанию
USE marketing_new;

-- открытие новой (горячей) дельты
BEGIN DELTA;

-- вставка двух записей в логическую таблицу sales (без опционального значения description)
UPSERT INTO sales 
       (id, transaction_date, product_code, product_units, store_id)
VALUES (200014, '2021-08-23 09:34:10', 'ABC0003', 3, 123), 
       (200012, '2021-08-23 20:05:56', 'ABC0001', 6, 234); 

-- закрытие дельты
COMMIT DELTA;

Вставка данных в логическую таблицы вне дельты 

-- вставка двух записей в логическую таблицу sales вне дельты
INSERT INTO marketing.sales 
VALUES (800011, '2021-07-11 23:34:10', 'ABC0004', 2, 123, 'Покупка по акции "1+1"'), 
       (800012, '2021-07-23 10:05:56', 'ABC0017', 5, 234, 'Покупка без акций')

Вставка данных во все столбцы standalone-таблицы 

-- вставка записей в standalone-таблицу, на которую указывает внешняя writable-таблица agreements_ext_write_adp
UPSERT INTO marketing.agreements_ext_write_adp 
VALUES (200, 444444, 'AB22222', '2022-02-08', '2022-02-09', '2024-02-09', ''), 
       (201, 555555, 'AB33333', '2022-02-10', '2022-02-11', '2025-02-11', 'Договор с ООО "Овал"');

Вставка данных в указанные столбцы standalone-таблицы 

-- вставка записей в standalone-таблицу, на которую указывает внешняя writable-таблица agreements_ext_write_adp,
--  без некоторых опциональных значений
UPSERT INTO marketing.agreements_ext_write_adp (id, client_id, number, signature_date)
VALUES (202, 999999, 'AB44444', '2022-01-01');

Вставка данных в указанные столбцы партиционированной таблицы 

-- выбор логической базы данных marketing в качестве базы данных по умолчанию
USE marketing;

-- открытие новой (горячей) дельты
BEGIN DELTA;

-- вставка трех записей в партиционированную таблицу sales_partitioned
UPSERT INTO sales_partitioned (id, transaction_date, store_id)
VALUES (500011, '2023-01-11 23:34:10', 124), 
       (600012, '2023-01-18 10:05:56', 234), 
       (600113, '2023-02-22 13:17:47', 123);

-- закрытие дельты
COMMIT DELTA;

Вставка данных в указанные столбцы партиции 

-- выбор логической базы данных marketing в качестве базы данных по умолчанию
USE marketing;

-- открытие новой (горячей) дельты
BEGIN DELTA;

-- вставка трех записей в партицию sales_jan_2023
---- последняя запись будет проигнорирована, так как дата транзакции в ней не принадлежит партиции sales_jan_2023
UPSERT INTO sales_jan_2023 (id, transaction_date, store_id)
VALUES (500011, '2023-01-11 23:34:10', 124), 
       (600012, '2023-01-18 10:05:56', 234), 
       (600113, '2023-02-22 13:17:47', 123);

-- закрытие дельты
COMMIT DELTA;

Перезапуск операции по вставке записей 

-- выбор логической базы данных marketing_new в качестве базы данных по умолчанию
USE marketing_new;

-- открытие новой (горячей) дельты
BEGIN DELTA;

-- вставка записи в логическую таблицу sales (без опционального значения description)       
UPSERT INTO sales
       (id, transaction_date, product_code, product_units, store_id)
VALUES (200015, '2021-10-15 10:11:01', 'ABC0003', 1, 123);

-- перезапуск обработки операции по вставке записи
RETRY UPSERT INTO sales
       (id, transaction_date, product_code, product_units, store_id)
VALUES (200015, '2021-10-15 10:11:01', 'ABC0003', 1, 123); 

-- закрытие дельты
COMMIT DELTA;