Solved

sql server query works in select but not in update.

Posted on 2016-09-12
3
59 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 10

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 34

Accepted Solution

by:
ste5an earned 500 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

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ms sql and asp dates 5 42
SQL 2012 Report Builder 3.0 query 2 21
SQL Server 2012 to SQL Server 2016 24 54
SQL Lag Function DateDiff 2 26
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

734 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