Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Update Not Null Column

Re: Update Not Null Column

From: Frank Hubeny <fhubeny_at_ntsource.com>
Date: Tue, 19 Oct 1999 23:56:55 -0500
Message-ID: <380D4B96.F9D59EAF@ntsource.com>


One way to get the error is if the select statement does not return any value.

You mentioned that example_table2 has less rows. It is possible that the dummy_id value you happened to select recently from the larger table, example_table, is not in any of the rows in example_table2. This would explain why the update worked in the past, but not currently.

To check if this is the case, find an update statement that fails with the 1407 error. Then check to see if there is a record in example_table2 that has the corresponding dummy_id required by the where clause.

Frank Hubeny

Rebecca Tongsinoon wrote:

> Hello,
> I am running an update statement in Oracle 8 on NT:
>
> update example_table A set (dummy_var, update_date) =
> (select dummy_var, update_date,
> from example_table2 B
> where A.dummy_id = B.dummy_id )
> where a.someothervar = 'somevalue'
>
> in this example, my dummy_var has no key constraints, but is a NOT NULL
> column in both tables. Example_table2 has LESS rows than Example_table but
> I don't see why this should matter.
>
> For some reason, I'm getting the following error:
>
> ORA-01407: cannot update ("USER"."TABLE"."COLUMN") to NULL for the
> dummy_var column
>
> Yet, when I do this query:
>
> select count(*) from example_table2
> where dummy_var is null
>
> My result is 0, which I expected since the column is NOT NULL anyway. When
> I run this same update using a cursor, it runs fine. However, this update
> without a cursor has worked before in production for the last month.
>
> Any suggestions? Much appreciated...
>
> --
> Posted via CNET Help.com
> http://www.help.com/
Received on Tue Oct 19 1999 - 23:56:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US