Re: Update table problem

From: <bialik_at_wis.weizmann.ac.il>
Date: Thu, 09 Apr 1998 14:56:40 -0600
Message-ID: <6gj95o$ts3$1@nnrp1.dejanews.com>

In article <01bd61ff$e93f33b0$1103000a_at_anmo-nt>, "Anker Møller" <anmo_at_int.tele.dk> wrote:
>
> When I run the following update
>
> UPDATE xal_supervisor.debtable xal
> SET xal.address1 =
> (SELECT REPLACE(RTRIM(SUBSTR(bcc.address1, 0, 30)),'*',' ')
> FROM bcc.bccuser bcc
> WHERE bcc.public_id = xal.accountnumber)
> WHERE xal.accountnumber IN
> (SELECT bcc.public_id
> FROM bcc.bccuser bcc
> WHERE bcc.address1 IS NOT NULL AND
> bcc.address1 <> '' AND
> xal.address1 = '' AND
> bcc.public_id = xal.accountnumber);
>
> I get the error
>
> FROM bcc.bccuser bcc
> *
> ERROR at line 6:
> ORA-01407: cannot update mandatory (NOT NULL) column to NULL
>
> But why? - I only selects NOT NULL colums in my where clause
>
>

Hi.
Actually, that's what you are trying to do! When in BCC.BCCUSER table you DON'T have a row with PUBLIC_ID = XAL.ACCOUNTNUMBER, the select statement returns NULL and is trying to set the value of XAL.ADDRESS1 field to NULL.

Michael.

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Thu Apr 09 1998 - 15:56:40 CDT