Thanks. I'll give it a try. The "rownum=1" is okay in this case because of
business rules.
"VC" <boston103_at_hotmail.com> wrote in message
news:GjRsc.115310$iF6.10755190_at_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 Wed May 26 2004 - 08:29:45 CDT
