Help with Oracle UPDATE statement

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 - 11:38:20 CDT