Re: update all rows such that doesn't cause duplicate

From: Mark D Powell <>
Date: Mon, 14 Dec 2009 12:11:23 -0800 (PST)
Message-ID: <>

On Dec 14, 2:51 pm, lora <> wrote:
> Hello all,
> I've a oracle DB in which one of the VARCHAR2 fields that is part of
> the key needs to have a substring replaced with another with no
> special $ character in front of it.
> However, when I do this replace, I notice that for a few rows, this
> would cause a duplicate row and hence get rejected.
> So, I'd like to formulate a SQL statement such that all rows would be
> updated as long as it doesn't lead to duplicates.
> Any pointers appreciated!
> UPDATE MYTAB SET request = REPLACE(request, '$myString', 'myString')
> WHERE (operation='myOperation') and request like '%$myString%';

Duplicate based on what? Building a unique index on the request column would prevent duplicates in the example query you posted.

HTH -- Mark D Powell -- Received on Mon Dec 14 2009 - 14:11:23 CST

Original text of this message