Update subquery error

[ibjolley]

Hello everyone. I know this has been discussed previously, but I'm have having issues with the following update statement:

UPDATE CPES_AllItems
SET ItemTypeID =
(SELECT basicinfo.BasicInfoItemtypeID
FROM basicinfo, CPES_Allitems
WHERE basicinfo.basicinfoid =
CPES_AllItems.basicinfoid)

I am trying to update the CPES_AllItems.ItemTypeID with BasicInfo.BasicInfoItemTypeID.

The error I receive says: Subquery returned more than 1 value. This is not permitted the subquery follows = or when the subquery is used as an expression.

I think my problem may be with the join. I appreciate any help/advise!

-andrew

 

[tlbroadbent]

Try the following syntax.

UPDATE CPES_AllItems
SET ItemTypeID = basicinfo.BasicInfoItemtypeID
FROM CPES_Allitems Inner Join basicinfo
ON CPES_AllItems.basicinfoid = basicinfo.basicinfoid Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[ibjolley]

That worked. Thank you very much Terry!

Have a good one!

-andrew