multi-column key update

From: Frederick Pierce <fpierce_at_fellspt.charm.net>
Date: 1997/04/29
Message-ID: <5k5i5v$593$1@news2.charm.net>#1/1

Hello,

I'm trying to update a table with a multi-column key selecting from another table and not having much luck.

The update query I used is based on the example in 4-404 in the SQL reference manual modified as shown here:

update
product_distribution pd
set
pd.shipment_date =
(

select
ts.shipment_date
from
tssf_info ts
where
pd.investigator_id = ts.investigator_id

and pd.product_name = ts.product_name
and pd.sequence_number = ts.sequence_number
and pd.volume_number = ts.volume_number

)

This puts the proper dates with the proper rows where matches are found. The problem is it nulls the shipment_date column everywhere else. I've tried various combinations of subqueries using shipment_date in.. etc. but the results with these have been the opposite, i.e. 0 rows are updated.

Any suggestions on how to do this would be appreciated. Hope this is sufficient information - the source table contains only the columns listed
(its structure could be modified but the destination table is configured
so adding a unique key etc. is not an option). Using Oracle 7.1.3

Thanks,

fdp

     Fred Pierce - fpierce@avialantic.com 	http://www.avialantic.com
Mid Atlantic Air Museum WWII Weekend June 6-8 		/maamww97.html
Jack B. Poage Airshow June 21-22 Westminster MD   	/poage97.html
  ----------------------------------------------------------------------

Received on Tue Apr 29 1997 - 00:00:00 CDT