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.
First - +1 on the question - very well written, lots of supporting information, and please accept a huge THANK YOU for providing enough information to solve the problem, including table def and statements to populate the table.
The only real issue with the first update was that the line in the WHERE
clause which subsets the NEW
table should be pulled out of the subquery and put into the WHERE
clause of the UPDATE
statement, as in:
update demo_table new
set (new.quantity, new.actual_cost) =
(
SELECT old.quantity, old.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
)
where new.trans_type = 9;
I had to fool with this one for quite a while to figure out what was going on. Interesting problem.
Share and enjoy.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments