SQL statement results

From: Doug Yates <dyatesNOdySPAM_at_acxiom.com.invalid>
Date: Wed, 29 Dec 1999 17:21:16 +1600
Message-ID: <2a42f762.304bbc44@usw-ex0108-062.remarq.com>

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

-------------------- ---------------- ---------------- -----
--------------- -------------------- ---------------- ------


---------- --------------------


15-DEC-99            P2W1J2PSX00E01                    28-
DEC-99            16-DEC-99
P2W1J2PSX00E01                    28-DEC-99

Update Execution:

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,

• Sent from AltaVista http://www.altavista.com Where you can also find related Web Pages, Images, Audios, Videos, News, and Shopping. Smart is Beautiful

Received on Tue Dec 28 1999 - 19:21:16 CST