sql - Update ignore unique constraint key if the valeu is already on the table. - ORACLE -
i have oracle command.
update table_numbers set mdn = concat(concat(substr(mdn,1,2),`9`,substr(mdn,3,9)) mdn `8%` , length(mdn)=10;
i have put number 9 on 3 position if number start 8 , have 10 digits.
let's suppose have 8187412868
, 81987412868
.
but 81987412868
, 8187412868
on table.
in case there no need update or remove, ignore unique constraint error execute whole query.
like
if (concat(concat(subsrtr(mdn,1,2),`9`,substr(mdn,3,9)) mdn `8%` , lenght(mdn)=10) on table, ignore else execute....
you use exists
operator pre-check if value exits (also, note fixes brackets , typos in op's query):
update table_numbers set mdn = concat(concat(substr(mdn, 1, 2), '9') ,substr(mdn, 3, 9)) mdn '8%' , length(mdn) = 10 , not exists (select * table_numbers b a.mdn = concat(concat(substr(b.mdn, 1, 2), '9'), substr(b.mdn, 3, 9))
Comments
Post a Comment