| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> UPDATE, subqueries, and NULL values
We have a situation where an update of a subquery works
correctly on one box, but not on another......there are no
errors generated but the values are NULL in one case. Has
anyone seen something similar to the following?
I am trying to use a view in an update statement to modify a table. I thought this was working with other tables but when I run it with the example below, it updates the master table to NULLs rather than the corresponding values from the update table.
Can anyone help with this?
Before Update:
SQLWKS> select * from external_ref
2>
MOC_ID INDEX_ID EXTERNAL_KEY
DATA_SOURCE_ID EXTERNAL_KEY_TYP FIRST_DATE
CAPTURE_DATE LAST_DATE
---------------- ----------------
------------------------------------------------------------
-------------------- ---------------- ----------------
-------------------- --------------------
--------------------
P2W1J2PSX00E01 P2V1CUXNE1XX01
OSL 01D01 28-DEC-99
15-DEC-99 28-DEC-99
Result of query:
SQLWKS> (select
2> er.capture_date capture_date, er.moc_id, er.index_id index_id,
3> er.last_date last_date,
4> peru.capture_date source_capture_date,
peru.moc_id source_moc_id,
5> peru.index_id source_index_id, sysdate
source_last_date
6> from external_ref er, prof_external_ref_update
peru
7> where er.external_key = peru.external_key and
8> er.external_key_type =
peru.external_key_type and
9> er.data_source_id = peru.data_source_id)
10>
CAPTURE_DATE MOC_ID INDEX_ID
LAST_DATE SOURCE_CAPTURE_DATE SOURCE_MOC_ID
SOURCE_INDEX_ID SOURCE_LAST_DATE
SQLWKS> update
2> (select
3> er.capture_date capture_date, er.moc_id,
er.index_id index_id,
4> er.last_date last_date,
5> peru.capture_date source_capture_date,
peru.moc_id source_moc_id,
6> peru.index_id source_index_id, sysdate
source_last_date
7> from external_ref er, prof_external_ref_update
peru
8> where er.external_key = peru.external_key and
9> er.external_key_type =
peru.external_key_type and
10> er.data_source_id = peru.data_source_id)
11> set moc_id = source_moc_id, index_id =
source_index_id,
12> capture_date = source_capture_date, last_date = source_last_date
13>
1 row processed.
After Update:
SQLWKS> select * from external_ref
2>
MOC_ID INDEX_ID EXTERNAL_KEY
DATA_SOURCE_ID EXTERNAL_KEY_TYP FIRST_DATE
CAPTURE_DATE LAST_DATE
---------------- ----------------
------------------------------------------------------------
-------------------- ---------------- ----------------
-------------------- --------------------
--------------------
P2V1CUXNE1XX01
OSL 01D01 28-DEC-99
28-DEC-99
Thanks,
 |
 |