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

From: joel garry <joel-garry_at_home.com>
Date: Mon, 14 Dec 2009 13:36:46 -0800 (PST)
Message-ID: <a2bef525-974f-4eeb-83b9-4081e2fd0c1a_at_u18g2000pro.googlegroups.com>



On Dec 14, 12: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)

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

--
_at_home.com is bogus.
Received on Mon Dec 14 2009 - 15:36:46 CST

Original text of this message