CODEWITHSUNDEEP
oracle-databaseprimary-keyduplicate-data
K. Weber
K. Weber

Reputation: 2773

Oracle avoid duplicate key exception

I have a query with regular expression which takes a string and splits words to insert in a table. This table has only 2 columns (TWEET and PALABRA) and both of them form a primary key.

The query is this:

INSERT INTO TWEET_PALABRAS (TWEET, PALABRA)
  SELECT 
         NEW_TWEET,
         TRANSLATE(regexp_substr(NEW_TITULO,
                                '[^[:punct:]|[:space:]]+',
                                 1,
                                 level),
                  'áéíóúÁÉÍÓÚ',
                  'aeiouAEIOU')
  FROM DUAL 
  where LENGTH(regexp_substr(NEW_TITULO, '[^[:punct:]|[:space:]]+', 1, level)) >= 5
  connect by regexp_substr(NEW_TITULO, '[^[:punct:]|[:space:]]+', 1, level) is not null;

If a word is repeated in a tweet I will have a duplicate key exception, the proper way to avoid this would be to add an ON DUPLICATE KEY UPDATE clause, but this is causing a syntax error:

INSERT INTO TWEET_PALABRAS (TWEET, PALABRA)
  SELECT 
        NEW_TWEET,
        TRANSLATE(regexp_substr(NEW_TITULO,
                                '[^[:punct:]|[:space:]]+',
                                1,
                                level), 
                  'áéíóúÁÉÍÓÚ',
                  'aeiouAEIOU')
   FROM DUAL 
  where LENGTH(regexp_substr(NEW_TITULO, '[^[:punct:]|[:space:]]+', 1, level)) >= 5
  connect by regexp_substr(NEW_TITULO, '[^[:punct:]|[:space:]]+', 1, level) is not null;
ON DUPLICATE KEY UPDATE TWEET=NEW_TWEET;

I don't need to update anything on duplicate key, just to avoid the exception, and plus, if duplicate key happens, the nexts words after the duplicate key should be inserted also. This code is inside a trigger with more actions after this one (so an exception block is not a solution).

Thank you

Upvotes: 0

Views: 798

Answers (1)

Yaroslav Shabalin
Yaroslav Shabalin

Reputation: 1644

Just add DISTINCT to your query to avoid duplicates:

INSERT INTO TWEET_PALABRAS
  (TWEET, PALABRA)
  SELECT DISTINCT NEW_TWEET,
                  TRANSLATE(regexp_substr(NEW_TITULO,
                                          '[^[:punct:]|[:space:]]+',
                                          1,
                                          level),
                            'áéíóúÁÉÍÓÚ',
                            'aeiouAEIOU')
    FROM DUAL
   where LENGTH(regexp_substr(NEW_TITULO,
                              '[^[:punct:]|[:space:]]+',
                              1,
                              level)) >= 5
  connect by regexp_substr(NEW_TITULO, '[^[:punct:]|[:space:]]+', 1, level) is not null;

Upvotes: 1

Related Questions