• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 85
  • Last Modified:

sql server query works in select but not in update.

I have a super query ID: 41792646 that gives me the sum of the ascii chararcters in a string word. The string only ever contains one word. if i do:

select word, dbo.func_ASCIIEncodeString(TblCurrentWords.word) as AsciiSum
from TblCurrentWords

Open in new window


I get output (the words are not duplicated in the tblcurrentwords) yet when I do:
UPDATE TblCurrentWords
SET AsciiEncode = (
select dbo.func_ASCIIEncodeString(TblCurrentWords.word)
from TblCurrentWords

)

Open in new window


I get error:
Msg 512, Level 16, State 1, Line 5
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

assistance would be appreciated.
0
PeterBaileyUk
Asked:
PeterBaileyUk
1 Solution
 
HuaMinChenSystem managerCommented:
Try
UPDATE TblCurrentWords
SET AsciiEncode = (
select top 1 dbo.func_ASCIIEncodeString(TblCurrentWords.word)
from TblCurrentWords
)

Open in new window

0
 
ste5anSenior DeveloperCommented:
Your SELECT return a tuple ( word, word, dbo.func_ASCIIEncodeString(word) ). This means there is a correlation.

You need this correlation also in your UPDATE. E.g.

UPDATE  W
SET     W.AsciiEncode = dbo.func_ASCIIEncodeString(W.word)
FROM    TblCurrentWords W;

Open in new window

0
 
PeterBaileyUkAuthor Commented:
thank you Ste5an
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now