Re: Help with Oracle UPDATE statement

Something like

update
(select b.PKey bPKey, b.CRef bCRef, a.CRef aCRef  from B, A
where A.BRef = b.PKey
and a.CRef <> b.CRef)
set bCref = aCref;

"TC" <cleavet_at_my-Deja.com> wrote in message news:663711ac.0209230838.6169a4c0_at_posting.google.com...
> Howdy,
>
> I need help formulating a specific UPDATE statement under Oracle
> 8.1.6. The situation:
>
> SCHEMA
>
> Table A optionally references Table B (a.BRef = b.PKey)
> Table A optionally references Table C (a.CRef = c.PKey)
> Table B must reference Table C (b.CRef = c.PKey)
>
>
> BUSINESS RULES
>
> 1) If table A references table B, then table A must reference table C.
>
> 2) Table A may reference table C without referencing table B.
>
> 3) If table A references table B, then table A and table B should
> reference the same record in table C.
>
>
> Due to a logic bug in our software, business rule #3 is no longer
> completely valid. I would like to formulate an UPDATE statement to
> correct this. The rule is that where a.CRef <> b.CRef and a.BRef =
> b.PKey, then set b.CRef = a.CRef.
>
> Under SQL Server, I would do the following:
>
> UPDATE B
> SET CRef = A.CRef
> FROM A INNER JOIN B ON BRef = BKey
> WHERE A.CRef <> B.CRef
>
> I am having a devil of a time putting together an update to handle
> this in Oracle, though! The best I could come up with was the
> following:
>
> UPDATE B
> SET CRef = (
> SELECT CRef
> FROM A
> WHERE BRef = BKey
> );
>
> It failed with an error 1407 (not null constraint violation). Can
> someone help? Thanks in advance, and I apologize if this is too
> convoluted.
>
> Tom C.
Received on Mon Sep 23 2002 - 12:21:24 CDT