Update to NULL

From: Alan <alan_at_erols.com>
Date: Tue, 25 May 2004 11:38:41 -0400
Message-ID: <2hh7jeFcegaeU1@uni-berlin.de>

I haven't run into this one before, and I am stumped (or having a bad day). I am trying to update one column in a table across a dblink to a value in one other table. The column is constrained to NOT NULL.

Using Oracle 8.1.7.4 on Windows 2000 SP4.

Here is the code:

CREATE TABLE SMALLGROUP.AGENT_SALES_REP_LINK
(

    SALES_REP_NBR VARCHAR2(2)     NULL,
    RSO_ASSOC     VARCHAR2(3)     NULL,
    AGENCY_CODE   VARCHAR2(6)     NULL

)
;

CREATE TABLE EXTRANET.CLIENT_LINK_RSL
(

    TIN            VARCHAR2(11) NOT NULL,
    USER_ID        VARCHAR2(50) NOT NULL,
    RSL_IDENTIFIER VARCHAR2(17) NOT NULL,
    USER_TYPE      VARCHAR2(3)  NOT NULL,
    RSO            VARCHAR2(25) NOT NULL,
    MGA_CODE       VARCHAR2(10)     NULL,
    GA_CODE        VARCHAR2(10)     NULL,
    REP            VARCHAR2(2)      NULL

)
;

There are no Unique, PK or FK constarints created yet.

SQL> UPDATE client_link_rsl_at_extranet cl
  2 SET rso =
  3 (
  4 SELECT NVL(rso_assoc, 'UNK' )
  5 FROM agent_sales_rep_link asrl
  6 WHERE asrl.agency_code = cl.rsl_identifier   7 AND ROWNUM = 1
  8 )
  9 ;
UPDATE client_link_rsl_at_extranet cl
*
ERROR at line 1:
ORA-01407: cannot update ("EXTRANET"."CLIENT_LINK_RSL"."RSO") to NULL ORA-02063: preceding line from EXTRANET

I also tried adding code so that the only the rows that have an rso_assoc value would be used in the update. Same results:

SQL> UPDATE client_link_rsl_at_extranet cl
  2 SET rso =
  3 (
  4 SELECT NVL(rso_assoc, 'UNK')
  5 FROM agent_sales_rep_link asrl
  6 WHERE agency_code = rsl_identifier
  7 AND rep IS NOT NULL
  8 AND rso IS NOT NULL
  9 AND ROWNUM = 1
10 AND rsl_identifier IN
11 (
12 SELECT agency_code
13 FROM agent_sales_rep_link
14 WHERE rso_assoc IS NOT NULL
15 )
16 )
17 ;
UPDATE client_link_rsl_at_extranet cl
*
ERROR at line 1:
ORA-01407: cannot update ("EXTRANET"."CLIENT_LINK_RSL"."RSO") to NULL ORA-02063: preceding line from EXTRANET Received on Tue May 25 2004 - 10:38:41 CDT