Need help w/ Update statement

[Bigced21]

What am I doing wrong in the code to receive this error?

i get an error saying this:
Server: Msg 512, Level 16, State 1, Line 1
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.

In the tbl hbc_zImport owner_state data type is a varchar and the state's are abbr. where in hbc_contact state_id data type is int. I have a tbl called hbc_state that contains name, abr, and fips code.
Here's the code I've written, the select statments works fine alone, but with the update it'll give me an error.

update HBC_Contact
set state_id =
(SELECT HBC_STATE.ID
FROM HBC_STATE, HBC_ZIMPORT
WHERE HBC_STATE.ABR = HBC_ZIMPORT.OWNER_STATE
AND HBC_ZIMPORT.OWNER_NAME = HBC_CONTACT.BUSINESS_NAME
)

 

[Katy44]

Have you tried running the subquery alone to see what is returned? It must return a single value.

 

[Katy44]

Sorry, I have just re-read your question, you have tried it, so does it just return one value?

 

[Bigced21]

but its suppose to update a tbl of 60k recs which have different states in some of the records for ex. tn, mo, ky, ind.

 

[Mercury2]

Hi Bigced21,

Have you tried using DISTINCT (or Unique ?) in the subquery ?

update HBC_Contact
set state_id =
(SELECT DISTINCT HBC_STATE.ID
FROM ...

HTH - Philippe

The pessimist says the glass is half empty. The optimist
says the glass is half full. The re-engineering person
says you have twice as much glass as you need

 

[Bigced21]

I tried that and i still get the 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.

 

[Mercury2]

I could try this, I'd change for JOIN predicates and add the ORDER BY clause

update HBC_Contact
set state_id =
(SELECT DISTINCT HBC_STATE.ID
FROM HBC_STATE
JOIN HBC_ZIMPORT
ON HBC_STATE.ABR = HBC_ZIMPORT.OWNER_STATE
AND HBC_ZIMPORT.OWNER_NAME = HBC_CONTACT.BUSINESS_NAME
ORDER BY HBC_STATE.ID
)

HTH this time - Philippe

 

[Mercury2]

BTW, seems bizarre that there is no link on the WHERE clause between the file to update HBC_CONTACT and the subquery ??