CODEWITHSUNDEEP
sqloracle-databaseselectoracle11goracle10g
Rogerio Camorim
Rogerio Camorim

Reputation: 381

Update ignore unique constraint key if the value is already on the table. - ORACLE

I have this oracle command.

UPDATE TABLE_NUMBERS
SET mdn = concat(concat(substr(mdn,1,2),`9`,substr(mdn,3,9))
WHERE mdn LIKE `8%` AND LENGTH(mdn)=10;

I have to put the number 9 on the 3 position if the number start with 8 and have 10 digits.

Let's suppose I have 8187412868, it will be 81987412868.

But 81987412868 and 8187412868 is already on the table.

In this case there is no need to update or remove, but i would like to ignore the unique constraint error to execute the whole query.

like 

    if (concat(concat(subsrtr(mdn,1,2),`9`,substr(mdn,3,9))
    WHERE mdn LIKE `8%` AND LENGHT(mdn)=10)
    already on the table, then ignore
    else
    execute....

Upvotes: 1

Views: 1601

Answers (1)

Mureinik
Mureinik

Reputation: 311448

You could use the exists operator to pre-check if this value exits (also, note I fixes the brackets and typos in the OP's query):

UPDATE table_numbers a
SET    mdn = CONCAT(CONCAT(SUBSTR(mdn, 1, 2), '9') ,SUBSTR(mdn, 3, 9))
WHERE  mdn LIKE '8%' AND 
       LENGTH(mdn) = 10 AND
       NOT EXISTS (SELECT *
                   FROM   table_numbers b
                   WHERE  a.mdn = CONCAT(CONCAT(SUBSTR(b.mdn, 1, 2), 
                                                '9'), 
                                         SUBSTR(b.mdn, 3, 9))

Upvotes: 2

Related Questions