Re: Update Not Null Column

Rebecca:

    I was looking at your update statement which looks fine to me, but maybe Oracle is confused on
what table the dummy_var and update_date belong to, I would try changing the statement to
the following:

    update example_table A set (A.dummy_var, A.update_date) =

           (select B.dummy_var, B.update_date,
            from example_table2 B
                where A.dummy_id = B.dummy_id )
        where a.someothervar = 'somevalue';

I know it's a long shot, but that is the best I can offer. Sorry, and good luck!
Tina

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 - 19:03:54 CDT