Update using subselect

[gotmilk2]

How can I tie the update table to a table in the subselect to ensure the update takes place on the correct rows. Example:

Update RDS_RFID_DATA
Set task_nbr =
(Select c.task_nbr
from RDS_RFID_DATA a, RDS_BLDG_AREA b,
INV_TASK c
Where a.loc_nbr = b.loc_nbr
And a.bin(1:3) = b.whse_aisle
And a.bin(4:2) = b.whse_bin
And a.bin(6:1) = b.whse_lvl
And a.bin(7:1) = b.whse_sect
And b.bldg_area_nbr = c.bldg_area_nbr)
Where rds_RFID_DATA.bin = ???

 

[MarcLodge]

Can you repeat the subselect on the where with an in clause and sub-selecting the key field?

I've got a feeling it won't let you do that though.

 

[MarcLodge]

Or can you link it back to itself as a correlated query? eg
Update RDS_RFID_DATA z
Set task_nbr =
(Select c.task_nbr
from RDS_RFID_DATA a, RDS_BLDG_AREA b,
INV_TASK c
Where a.loc_nbr = b.loc_nbr
And a.bin(1:3) = b.whse_aisle
And a.bin(4:2) = b.whse_bin
And a.bin(6:1) = b.whse_lvl
And a.bin(7:1) = b.whse_sect
And b.bldg_area_nbr = c.bldg_area_nbr
And a.bin = z.bin)

 

[Mercury2]

Not sure but try this out

Update RDS_RFID_DATA
Set task_nbr =
( Select c.task_nbr
  from RDS_RFID_DATA a, RDS_BLDG_AREA b, INV_TASK c
  Where  a.loc_nbr = b.loc_nbr
   And a.bin(1:3) = b.whse_aisle
   And a.bin(4:2) = b.whse_bin
   And a.bin(6:1) = b.whse_lvl
   And a.bin(7:1) = b.whse_sect
   And b.bldg_area_nbr = c.bldg_area_nbr )

Where EXISTS 
  ( Select 1 from INV_TASK c 
    Where a.loc_nbr = b.loc_nbr
     And a.bin(1:3) = b.whse_aisle
     And a.bin(4:2) = b.whse_bin
     And a.bin(6:1) = b.whse_lvl
     And a.bin(7:1) = b.whse_sect
     And b.bldg_area_nbr = c.bldg_area_nbr )