Сложный запрос 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