Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sql server query works in select but not in update.

Posted on 2016-09-12
3
Medium Priority
?
77 Views
Last Modified: 2016-09-12
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
Comment
Question by:PeterBaileyUk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 41793806
Try
UPDATE TblCurrentWords
SET AsciiEncode = (
select top 1 dbo.func_ASCIIEncodeString(TblCurrentWords.word)
from TblCurrentWords
)

Open in new window

0
 
LVL 35

Accepted Solution

by:
ste5an earned 2000 total points
ID: 41793810
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
 

Author Closing Comment

by:PeterBaileyUk
ID: 41793819
thank you Ste5an
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question