Сложный запрос Oracle: INSERT + SELECT CONNECT BY + ON DUPLICATE KEY

У меня есть таблица для подсчета числа слов, WORD_COUNT(WORD, TOTAL), который обновляется в триггере над другой таблицей. Это делается в виде иерархического запроса, например:

Создание слова и всего =1:

INSERT INTO WORD_COUNT(TOTAL, WORD)
  SELECT 1, TRANSLATE(regexp_substr(:NEW.TWEET, '[^[:punct:]|[:space:]]+', 1, level),
                            'áéíóúÁÉÍÓÚ',
                            'aeiouAEIOU')
  FROM DUAL
  WHERE LENGTH(regexp_substr(:NEW.TWEET, '[^[:punct:]|[:space:]]+', 1, level)) >= 5
  CONNECT BY regexp_substr(:NEW.TWEET, '[^[:punct:]|[:space:]]+', 1, level) IS NOT NULL;

Эта работа идеально подходит, она разбивает фразу (твит) на слова и вставляет их в WORD_COUNT.

Проблема сейчас в том, что когда я хочу увеличить общее количество на дубликат ключа (WORD является первичным ключом), мне нужно добавить ON DUPLICATE KEY пункт, который, кажется, не ладит с CONNECT BY пункт.

Этот запрос не компилируется:

INSERT INTO WORD_COUNT(TOTAL, WORD)
  SELECT 1, TRANSLATE(regexp_substr(:NEW.TWEET, '[^[:punct:]|[:space:]]+', 1, level),
                            'áéíóúÁÉÍÓÚ',
                            'aeiouAEIOU')
  FROM DUAL
  WHERE LENGTH(regexp_substr(:NEW.TWEET, '[^[:punct:]|[:space:]]+', 1, level)) >= 5
  CONNECT BY regexp_substr(:NEW.TWEET, '[^[:punct:]|[:space:]]+', 1, level) IS NOT NULL
  ON DUPLICATE KEY UPDATE TOTAL=TOTAL+1;

И этот тоже

INSERT INTO WORD_COUNT(TOTAL, WORD)
WITH WORDS(WORD) AS
 (SELECT DISTINCT 1,
         TRANSLATE(regexp_substr(:NEW.TWEET, '[^[:punct:]|[:space:]]+', 1, level),
                            'áéíóúÁÉÍÓÚ',
                            'aeiouAEIOU')
    FROM DUAL
    WHERE LENGTH(regexp_substr(:NEW.TWEET, '[^[:punct:]|[:space:]]+', 1, level)) >= 5
    CONNECT BY regexp_substr(:NEW.TWEET, '[^[:punct:]|[:space:]]+', 1, level) IS NOT NULL)
SELECT WORD FROM WORD_COUNT
ON DUPLICATE KEY UPDATE TOTAL = TOTAL + 1;

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

Спасибо

Источник

2 ответа

Решение

Это должно быть возможно с помощью слияния:

MERGE INTO WORD_COUNT WC
USING 
(
  SELECT DISTINCT 1,
         TRANSLATE(regexp_substr(:NEW.TWEET, '[^[:punct:]|[:space:]]+', 1, level),
                            'áéíóúÁÉÍÓÚ',
                            'aeiouAEIOU')
    FROM DUAL
    WHERE LENGTH(regexp_substr(:NEW.TWEET, '[^[:punct:]|[:space:]]+', 1, level)) >= 5
    CONNECT BY regexp_substr(:NEW.TWEET, '[^[:punct:]|[:space:]]+', 1, level) IS NOT NULL
) NW
ON (WC.WORD = NW.WORD)
WHEN MATCHED THEN UPDATE SET WC.TOTAL = WC.TOTAL + 1
WHEN NOT MATCHED THEN INSERT(TOTAL, WORD) VALUES(NW.TOTAL, NW.WORD);
Источник

Как я знаю, oracle db не поддерживает предложение "по дублирующему ключу". Вы должны попробовать использовать предложение MERGE.

Я думаю, что этот вопрос похож на: Oracle: ON DUPLICATE KEY UPDATE

Источник
Другие вопросы по тегам