Oracle complex query: INSERT + SELECT CONNECT BY + ON DUPLICATE KEY

Question

I have a table to count word occurences, WORD_COUNT(WORD, TOTAL), which is updated in a trigger over another table. This is done in a hierachichal query like this:

Creation of word and 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;

This work perfect, it splits a phrase (tweet) into words and inserts them in WORD_COUNT.

The problem now is when I want to increment total on duplicate key (WORD is primary key), I need to add an ON DUPLICATE KEY clause which seems not to get on very well with CONNECT BY clause.

This query doesn't compile:

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;

And this one neither:

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;

As this is happening inside a trigger of a high traffic table I would like to solve this in a single query, but maybe it's time to think of an intermediate table, is it?

Thank you

Answer 1

This should be possible using a merge:

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);