Multi-column update from SAME table, different rows, in Oracle, with not-null column: Receiving error 01407

I'm getting an error I do not understand.

I have an Oracle database (11g, I think) with a table that has a multi-column unique identifier. It's a transaction table, taking a before and after snapshot of the data from a different table. So far so good, that's all working fine.

We had a bug in our code and I'm trying to fix the data now that the bug is fixed. The bug resulted in the 'after' snapshot having two columns zeroed out in a circumstance where they should have kept their 'before'. So I'm going to update the 'after' snapshot (identified by a transaction type number) to have the same values in those two columns.

I've found all sorts of great help on the multi-column update thing, and I think I have it, except...it generates an error that it cannot update a not-null column to null, when there is no way I can see a null getting in there. Obviously, I am doing something wrong; I just can't tell what.

In some cases, one column can be null - there's an object type column that defines whether that record can have a null value.

The table has this data in it, say:

object_name      object_type   trans_type   trans_date   quantity    actual_cost
no-quantity      1             1            04/16/2014   {null}      20.00
no-quantity      1             9            04/16/2014   {null}       0.00
needs quantity   2             1            04/16/2014   3           15.00
needs quantity   2             9            04/16/2014   0            0.00 

So I need to update the second row (no-quantity, trans_type 9) to actual_cost of 20; quantity can stay null. And I need the 4th row to be updated to quantity of 3, actual_cost of 15.

Here is the query I'm trying to run that is failing - create and insert statements are below:

update  demo_table new 
set     (quantity, actual_cost) = 
        (
        SELECT  quantity, actual_cost
        FROM    demo_table old
        WHERE   old.object_name = new.object_name
            and old.object_type = new.object_type
            and old.trans_date = new.trans_date
            and old.trans_type = 1
            and new.trans_type = 9
)

When I run this I get:

ORA-01407: cannot update ("myschema"."DEMO_TABLE"."ACTUAL_COST") to NULL

(I've tried the other way - update (select col1, col2, etc) - and the table's not set up right for it. Too bad, because that way looked easier to understand...)

Do I maybe need to have an outer where too? The other posts didn't indicate that I did, and I don't know how I would formulate it anyway.

I even get the not-null error on this attempt:

update  demo_table new 
set     (actual_cost) = 
        (
        SELECT  actual_cost
        FROM    demo_table old
        WHERE   old.object_name = new.object_name
            and old.object_type = new.object_type
            and old.trans_date = new.trans_date
            and old.trans_type = 1
            and new.trans_type = 9
            and old.object_type = 2
)

object_type of 2 doesn't have a null quantity, and anyway I'm not trying quantity in this one...

The select statement I run to check the actual where clause is correct looks like this:

SELECT  old.object_name as old_name, old.object_type as old_type, old.trans_type as old_trans, old.trans_date as old_date,
    new.object_name as new_name, new.object_type as new_type, new.trans_type as new_trans, new.trans_date as new_date,
    old.quantity as old_quantity, old.actual_cost as old_cost, new.quantity as new_quantity, new.actual_cost as new_cost
FROM    demo_table old, demo_table new
WHERE    old.object_name = new.object_name
    and old.object_type = new.object_type
    and old.trans_date = new.trans_date
    and old.trans_type = 1
    and new.trans_type = 9

That gets the right values, the right old/new fields, only 2 rows returned, as I expect.

Here are my create-and-insert statements:

create table demo_table (
    object_name varchar2(30) not null,
    object_type number(3) not null,
    trans_type number(3) not null,
    trans_date timestamp(6) not null,
    quantity number(3),
    actual_cost number(17,2) not null
)

insert into demo_table (object_name, object_type, trans_type, trans_date, quantity, actual_cost)
values (
'no-quantity', 1, 1, '16-APR-14', null, 20
)

insert into demo_table (object_name, object_type, trans_type, trans_date, quantity, actual_cost)
values (
'no-quantity', 1, 9, '16-APR-14', null, 0
)

insert into demo_table (object_name, object_type, trans_type, trans_date, quantity, actual_cost)
values (
'needs quantity', 2, 1, '16-APR-14', 3, 15
)

insert into demo_table (object_name, object_type, trans_type, trans_date, quantity, actual_cost)
values (
'needs quantity', 2, 9, '16-APR-14', 0, 0
)

I hope my problem is clear. I did look around a lot, but I couldn't find anything that looked like it matched quite closely enough. The same-table thing is not really covered, and the not-nullable column is not really covered. (Or rather, it is, but I couldn't see how the problems described affected my situation.)

I know the table setup is, shall we say, far from ideal. Can't fix it, tonight.