update all rows such that doesn't cause duplicate

[lora]

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%';

[Mark D Powell]

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 --

[lora]

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)

> HTH -- Mark D Powell --- Hide quoted text -
>
> - Show quoted text -

[joel garry]

On Dec 14, 12:40 pm, lora <anjela_...@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)

Haven't really thought it through, but perhaps you can use a merge
command, when matched then do nothing or delete and insert (or
whatever your rule is), when not matched then insert?

something like:

merge into mytab a
using
(select substr(field1...) fieldname1,field2 fieldname2... from mytab)
b
on a.fieldname1=b.fieldname1 and a.fieldname2=b.fieldname2...
when matched then
update someflagtolookatlater
when not matched then
insert [regular insert statement with field names and values]
/

Apologies if I have the syntax wrong, this is just off the top of my
head. I've done similar things with the b aliasing dual, I'm guessing
a self-join will do the same thing. More examples of merge:
http://www.idevelopment.info/data/Oracle/DBA_tips/SQL/SQL_14.shtml

jg
--
@home.com is bogus.

[Mark D Powell]

> > - 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 )