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

score 2 · Accepted Answer · answered Mar 04 '14 at 08:57

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

score 0 · Answer 2 · edited May 23 '17 at 11:57

0

As I know oracle db doesn't support "on duplicate key" clause. You should try use MERGE clause.

I think it's similar question to: Oracle: ON DUPLICATE KEY UPDATE

edited May 23 '17 at 11:57

Community

1
1

answered Mar 04 '14 at 09:02

rtbf

1,509
1
16
35

Maybe it was added recently: http://docs.oracle.com/cd/E17952_01/refman-5.0-en/insert-on-duplicate.html – K. Weber Mar 04 '14 at 10:01
1

Unfortunately not, link concerns MySQL. Go one level up in your link and you receive "MySQL 5.0 Reference Manual": http://docs.oracle.com/cd/E17952_01/refman-5.0-en/ – rtbf Mar 04 '14 at 10:03

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

2 Answers2