Re: Update to NULL

From: VC <boston103_at_hotmail.com>
Date: Wed, 26 May 2004 00:14:30 GMT
Message-ID: <GjRsc.115310$iF6.10755190@attbi_s02>

Hi Alan,

Pls. see below:

"Alan" <alan_at_erols.com> wrote in message news:2hhcboFcln4hU1_at_uni-berlin.de...
> "Turkbear" <john.g_at_dot.spamfree.com> wrote in message
> news:rnt6b0h3jvk4biau5em8642f11o9dg5bjt_at_4ax.com...
> > "Alan" <alan_at_erols.com> wrote:
> >
> > >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
> > >
> > >
> > >
> > >
> > >
> >
> > What output do you get when you run:
> > --------------------------------------------------
> > SELECT NVL(rso_assoc, 'UNK' )
> > FROM agent_sales_rep_link asrl,client_link_rsl_at_extranet cl
> > WHERE asrl.agency_code = cl.rsl_identifier
> > AND ROWNUM = 1
> > -------------------------------------------------------
> >
> >

>
>

> Tried that already. I do get one row, as expected, with a real value:

>

> 1 SELECT NVL(rso_assoc, 'UNK' )
> 2 FROM agent_sales_rep_link asrl,client_link_rsl_at_extranet cl
> 3 WHERE asrl.agency_code = cl.rsl_identifier
> 4* AND ROWNUM = 1
> 5 /

>

> NVL
> ---
> WAS
Your update logic is wrong because you trying to update the whole table, not just matching rows. For matching rows 'SET rs = ... ' uses a value from the second table (or 'UNK') whilst for the rest of the f table being updated this value is, naturally null, and therefore, because of the NOT NULL constraint, Oracle generates an error.

The 'correct' way would be:

UPDATE client_link_rsl_at_extranet cl
  SET rso =
  (
  SELECT NVL(rso_assoc, 'UNK' )
  FROM agent_sales_rep_link asrl
  WHERE asrl.agency_code = cl.rsl_identifier   AND ROWNUM = 1
  )
where exists (
  select 1
  from agent_sales_rep_link asrl
  where asrl.agency_code = cl.rsl_identifier ) ;

I do not like this rownum=1 either as it means that you update your first table with the first random value(among possibly many candidate values) from the second table matching row(s). You'd want to get rid of the rownum so that the result of the update would be predictable.

Alternatively, you may consider updating a join of the two tables (if possible).

VC Received on Tue May 25 2004 - 19:14:30 CDT