CODEWITHSUNDEEP
sqloracle-databasesqlplus
larsvemund
larsvemund

Reputation: 63

Use of IGNORE_DUP_KEY in Oracle database (sqlplus)

I'm trying to run the following code on less than 10000 rows, but I'm getting

ORA-00001: unique constraint (constraint_name) violated (not unexpected).

UPDATE table1
   SET EMAILADRESSE = replace(EMAILADRESSE,'@domain1.no','@domain2.no')
 WHERE EMAILADRESSE LIKE '%@domain1.no' ;

Tried IGNORE_DUP_KEY, but this is not supported in oracle/SQL*PLUS as far as my research shows. Do you have any alternatives for me?

Upvotes: 4

Views: 705

Answers (2)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

Another one with NOT EXISTS!

UPDATE table1 t1
   SET EMAILADRESSE = replace(EMAILADRESSE,'@domain1.no','@domain2.no')
 WHERE EMAILADRESSE LIKE '%@domain1.no' 
 AND NOT EXISTS
 (SELECT 'X' FROM table1 t2 WHERE t2.EMAILADRESSE = replace(t1.EMAILADRESSE,'@domain1.no','@domain2.no'));

Upvotes: 1

IndoKnight
IndoKnight

Reputation: 1864

Firstly, it appears looking at the exception message EMAILADRESSE column has a unique contraint. You are trying to update table1 by changing email domain for which at least one of email addresses with replaced domain name already exists.

  UPDATE table1
    SET EMAILADRESSE = replace(EMAILADRESSE,'@domain1.no','@domain2.no')
    WHERE EMAILADRESSE LIKE '%@domain1.no' AND   replace(EMAILADRESSE,'@domain1.no','@domain2.no') NOT IN (SELECT EMAILADRESSE FROM table1)

Upvotes: 0

Related Questions