script error/question from quasi-newbie-

[joat25]

hi everyone, TIA for any help/suggestions.

i'm trying to run the following script:
UPDATE salesorderdelivery
SET salesorderdelivery.sod_csa_recordid = ( SELECT shiptospecial.csarecordid
FROM shiptospecial
WHERE shiptospecial.somrecordid = salesorderdelivery.sod_som_recordid)
WHERE EXISTS
( SELECT shiptospecial.csarecordid
FROM shiptospecial
WHERE shiptospecial.somrecordid = salesorderdelivery.sod_som_recordid);

I'm basically trying to modify any records in the salesorderdelivery table that have the same somrecordid from the shiptospecial table with the csarecordid from the shiptospecial table. the problem is that the script returns the following error:
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.
I know there could be multiple (same) somrecordids in the salesorderdelivery table, but can't I modify/update every one?

thanks,

 

[gradley]

See if this works for you. I haven't tried it since I don't have a copy of your tables but it should work.

UPDATE salesorderdelivery  
SET sod_csa_recordid = sts.csarecordid
FROM salesorderdelivery sod
INNER JOIN shiptospecial sts
on sts.somrecordid = sod.sod_som_recordid