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

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Tue, 15 Dec 2009 10:05:02 -0800 (PST)
Message-ID: <30aa5ce7-b491-446d-a568-dde86631ecd5_at_m3g2000yqf.googlegroups.com>

On Dec 14, 3:40 pm, lora <anjela_..._at_yahoo.com> wrote:
> So, I'm trying to replace all rows that have $myString with myString
> and this particular field is already a primary key field. The primary
> key is already defined for this table so performing the update gives
> me a constraint violation.
>
> However, what I want to do is in the SQL itself perform the update of
> the replace only if the resulting replace will cause no duplicates for
> that field.
>
> I tried fooling around with select replace and NOT in, but didn't get
> this to work. However, I'm sure there is a way! Thanks
>
> SELECT REPLACE(request, '$myString', 'myString') as A FROM MYTAB WHERE
> (myOperation='myOp') and request like '%myString%' and A not in
> (select request from MYTAB)
>
> On Dec 14, 3:11 pm, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
>
>
>
> > 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 --- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

You should be able to write an update statement with a where clause that checks that the updated string value does not match an existing PK column value.

update table_a a
set keycol = the_replace
where not exists ( select 'X' from table_a b where b.keycol = the_replace )

HTH -- Mark D Powell -- Received on Tue Dec 15 2009 - 12:05:02 CST