How to update many records about same table

Question

0.00/5 (No votes)

See more:

SQL

update table1
set column2 = dbo.fn_function(t.column1)
from table1 t
where t.user='peter'

The update execute shows the next error: "subquery return more than one value"

What I'm doing wrong? if the fn_function return one value?

Not works without "from" shows the same error

SQL

update table1
set column2 = dbo.fn_function(column1)
where user='peter'

Posted 19-Sep-13 9:17am

Ejrr1085

Updated 19-Sep-13 9:34am

v5

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Angel Gunther · Accepted Answer · 2013-09-19T09:25:00

Solution 1

You don't need the "from" line

SQL

update table1
set column2 = dbo.fn_function(t.column1)

get rid of this line
from table1 t

where t.user='peter'

since you are updating the same table you are checking the requirement "=peter" on.

Posted 19-Sep-13 9:25am

Angel Gunther

Comments

Ejrr1085 19-Sep-13 15:38pm

Not works without "from" shows the same error

update table1
set column2 = dbo.fn_function(column1)
where user='peter'

Ejrr1085 19-Sep-13 16:28pm

Sorry I found the error, the function has a select with a "beetween", and "the beetween" makes return 2 values. In my case is better use > and < or >= or <= thanks

Angel Gunther · Accepted Answer · 2013-09-19T09:38:00

Solution 2

Are you sure that your function call doesn't return more than one response for "column1"? It looks like the function is only using the parameter "column1", and getting multiple results back, so the query doesn't know which function result to use.

Posted 19-Sep-13 9:38am

Angel Gunther

Comments

Ejrr1085 19-Sep-13 15:43pm

the function return an only value no query or table. The column1 is a hour value and the function returns a varchar with the value if is in a range, therange value go to column2, column2 too is a varchar.

Ejrr1085 19-Sep-13 16:27pm

Sorry I found the error, the function has a select with a "beetween", and "the beetween" makes return 2 values. In my case is better use > and < or >= or <= thanks

Angel Gunther · Accepted Answer · 2013-09-19T10:18:00

Hmmm...

I've recreated this on my server as so

SQL

update dbo.SalesmanList
set lengthofRepCode = dbo.tester(RepCode)
where Name = 'Alan Lese'

the dbo.tester function doesn't reference any tables, just takes the varchar data parameter given and returns the length of the string, and then updates the table column oldaocpersno to whatever that is. I set up two records for the Alan Lese name, but with different length data in the RepCode column, and it worked perfect for me.

I'd really need to see your function i think to help you further, sorry.