Thanks for the question, Duane.
Asked: August 30, 2016 - 5:27 pm UTC
Last updated: August 31, 2016 - 2:22 am UTC
Version: 10G
Viewed 1000+ times
You Asked
PS - I'm using Toad 4 to do this.....
I have a database that has a table called parts_master with many columns, including....
pn varchar2(40)
list_price Number(19,2)
Mfg_auto_key Number
list_price_date(date)
I have a column:
select pn, list_price, mfg_auto_key, list_price_date from parts_master where pn = '3003988';
Yields:
PN LIST_PRICE MFG_AUTO_KEY LIST_PRICE_DATE
3003988 2 1/1/2016 12:00:00 AM
I can update the date field, but not the list_price field. This query says successful, but list_price remains NULL.
update parts_master set list_price = 6 , list_price_date = to_date('1/2/2016','mm/dd/yyyy') where pn = '3003988'
WHY !!!??
I've tried putting the number in single quotes and I've used to_number() function.
If I try to update a list_price on a row that contains a value, it ends up null as well.
and Connor said...
Well... in the most basic of test cases, all is well:
<code>
SQL> create table parts_master (
2 pn varchar2(40)
3 ,list_price Number(19,2)
4 ,Mfg_auto_key Number
5 ,list_price_date date
6 );
Table created.
SQL>
SQL> insert into parts_master values ('3003988', null, 2 ,to_date('01/01/2016','mm/dd/yyyy'));
1 row created.
SQL>
SQL> select pn, list_price, mfg_auto_key, list_price_date from parts_master where pn = '3003988';
PN LIST_PRICE MFG_AUTO_KEY LIST_PRIC
---------------------------------------- ---------- ------------ ---------
3003988 2 01-JAN-16
1 row selected.
SQL>
SQL> update parts_master set list_price = 6 , list_price_date = to_date('01/02/2016','mm/dd/yyyy') where pn = '3003988';
1 row updated.
SQL>
SQL> select pn, list_price, mfg_auto_key, list_price_date from parts_master where pn = '3003988';
PN LIST_PRICE MFG_AUTO_KEY LIST_PRIC
---------------------------------------- ---------- ------------ ---------
3003988 6 2 02-JAN-16
1 row selected.
<code>
So that tells us that something is possibly not all as it seems on *your* system.
Things to look for:
- can you reproduce the error in SQLPlus (ie, using something my demo above), in particular, so we can see the "1 row updated" output to be confident the row is indeed being updated.
- is there a trigger on the table which could be altering the value
- any RLS/VPD security predicates ?
Is this answer out of date? If it is, please let us know via a Comment