Update Subquery Problem 2

[Signit]

I am trying to retro fit a foreign key into an exisisting table. I've created records in a table called wfa_log based on exisisting records in the wfa_header table. Now I am trying to place the wfa_log_id values into the wfa_header table. When I run the below query I get the following error message:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Any help would be great!

begin tran
update wfa_header
   set wfa_log_id = (select distinct z.wfa_log_id
                       from wfa_header x,
                            machine y,
                            wfa_log z
                      where x.prop_num_or_vendor_serial_num = y.sandia_prop_num
                        and y.machine_sk = z.machine_sk)
  from (select distinct x.wfa_header_id
          from wfa_header x,
               machine y,
               wfa_log z
         where x.prop_num_or_vendor_serial_num = y.sandia_prop_num
           and y.machine_sk = z.machine_sk) as t3
 where wfa_header.wfa_header_id = t3.wfa_header_id
rollback tran

 

[PHV]

Why not simply this ?[tt]
update wfa_header
set wfa_log_id = z.wfa_log_id
from wfa_header x,
machine y,
wfa_log z
where x.prop_num_or_vendor_serial_num = y.sandia_prop_num
and y.machine_sk = z.machine_s[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Signit]

Why is it that the simplest answer is usually the best?! Thank you for the excellent suggestion.