Re: multi-column key update

<snip>
> 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
> )
>

<snip>

Fred, the problem is you don't have a where clause in your update statement, so it will update every row, and if it doesn't find a match it has to put in a NULL. One trick is to use NVL on the ts.shipment_date column so that if the column in the select comes back null you use the original column value from the pd table, like this:

select nvl(ts.shipment_date,pd.shipment_date) ...

I don't have my manual handy, so check the nvl syntax. The problem with this is that it will still update every row. And you may have to change the subquery to do an outer join to get the nvl to work. I can think of other ways using the where clause in the update statement or an explicit PL/SQL cursor loop, but nvl is the easiest I can think of right now.

Someone else may come up with a better way, but at least this should work.

--
---
Allen Kirby			AT&T ITS Production Services
akirby_at_att.com			Alpharetta, GA.