Update problem

[KenCunningham]

Hi folks. I'm probably being particularly dumb this morning, but I'm having problems updating a column using a select from another table. The sql I'm using is:

update property p
set p.management_area_code = (select n.new_ref
from new_allocs n
where p.management_area_code  = n.old_ref
and p.property_class = '1')
/

but this comes back at me with:

update property p
*
ERROR at line 1:
ORA-01407: cannot update ("BULL"."PROPERTY"."MANAGEMENT_AREA_CODE") to NULL

I may be missing something obvious here (there are no nulls in the source table!), but can't for the life of me see it. Any advice on what is wrong here, or alternative approaches, would be welcome. TIA.

 

[oradba101]

Good morning, Ken

Perhaps by reversing the WHERE clause to:

WHERE n.old_ref = p.management_area_code

Regards,


William Chadbourne
Oracle DBA

 

[KenCunningham]

Hi William and thanks for the idea. Unfortunately, reversing the where clause gives the same result. Any other ideas gratefully received.

 

[KenCunningham]

Hmm. Doing a lot of digging, came up with this, which appears to have worked, though I'm not quite sure why!!

update property
set management_area_code = (select new_ref
from new_allocs
where old_ref = management_area_code
and property_class = '1')
where exists
(select *
from new_allocs
where old_ref = management_area_code
and property_class = '1')
/

I also added an index on new_allocs, old_ref as suggested somewhere. If anyone's willing to analyse/explain this, please feel free!

 

[taupirho]

Call me a doubting thomas but please post the result of

select count(*) from new_allocs
where new_ref is null

If this comes back zero (which I doubt) try and modify your existing update to:-


update property p
set p.management_area_code = (select NVL(n.new_ref,'XXX')
from new_allocs n
where p.management_area_code = n.old_ref
and p.property_class = '1')


and see what happens.

Cheers.

 

[KenCunningham]

Just to confirm:

TRAIN(35.0)> select count(*) from new_allocs
2 where new_ref is null
3 /


COUNT(*)
--------------
0

Thanks.

 

[dbomrrsm]

update property p
set p.management_area_code = (select n.new_ref
from new_allocs n
where p.management_area_code  = n.old_ref
and p.property_class = '1'
and n.new_ref is not null)
/

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[skunkmaster]

Your original UPDATE statement will update the column management_area_code with the result of your select from new_allocs but any other records (the ones that didn't match) in your property table will have this column updated with NULL. Remember you have no WHERE clause in your UPDATE statement hence the need for your WHERE EXISTS clause that worked.
I assume that the column management_area_code is a NOT NULL column and that is why you cannot update it for the non-matching records.

Cheers
Kev

 

[KenCunningham]

Kev - thanks. You are quite correct, the column is a NOT NULL, hence the original problem. Should it not have been, I find it a little perverse of Oracle to update to null, given the original requirement. I guess there must be good reasoning for this, but I'm sure it will catch out many unwary users.