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

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Mon, 14 Dec 2009 12:11:23 -0800 (PST)
Message-ID: <4f36f922-682d-4c00-b656-79ee3ff3a018_at_m3g2000yqf.googlegroups.com>



On Dec 14, 2:51 pm, lora <anjela_..._at_yahoo.com> 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