Subquery returned more than 1 value

Question

Subquery returned more than 1 value

Carlo Ter Burg

Newbie

Points: 4
More actions
June 11, 2004 at 6:32 am

#359838

Hello,

I want to update a field based on a field in the same table
The statement I use is the following:
UPDATE    tblRelatie
SET              BrancheID = 1
WHERE     (LEFT(BIKCode_FK, 2) = '01')
I also tried to rewrite the code like this
UPDATE tblRelatie
SET brancheID = 1
WHERE relatieid IN
   (SELECT relatieid
   FROM tblRelatie
   WHERE Left(Bikcode_FK,2) ='01')
But that doesn't work either
Any help will be appreciated.

Thanks,

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Tal Mcmahon SSC Eights! Points: 850 More actions · Answer 1

hello,

there seems to be nothing wrong with your code. The error you referred to in the subject would not come into play in this instance.

the statement:

UPDATE employee

SET fname = fname + '!'

WHERE (LEFT(lname, 1) = 'a')

add ! to my employees table just fine

you must have more in your proc

sorry

tal mcmahon

Kindest Regards,

Tal Mcmahon

Carlo Ter Burg Newbie Points: 4 More actions · Answer 2

Hello,

I tried running this just as a query. Not as a stored procedure or a package. Just right in the Query Analyzer.

I also tried to link the tables in an Access database and run the query there. That does work and after a count Access returns the correct number of records, but when I look in the enterprise manager, things are still the same.

Tal Mcmahon SSC Eights! Points: 850 More actions · Answer 3

could you post your whole stored proc?

there is more here than you think

tal

Kindest Regards,

Tal Mcmahon

Greg Grow SSCrazy Points: 2386 More actions · Answer 4

Please post the code.

A couple of areas to check. If you "SET" a variable, make sure that the select statement doesn't return more than one row. In your select statement, if you have a subquery, make sure it only gets one row. If the subquery is correlated to your joins, make sure it only gets one row based on the correlation. This holds true for a subquery or correlated subquery in your JOINS or WHERE clause.

G

Carlo Ter Burg Newbie Points: 4 More actions · Answer 5

Hello,

There is no more code. I tried to run it in the query analyzer and it didn't work. How should the statement below be adjusted so that it returns only one row at the time.

UPDATE tblRelatie

SET BrancheID = 1

WHERE (LEFT(BIKCode_FK, 2) = '01')

Carlo

Carlo Ter Burg Newbie Points: 4 More actions · Answer 6

Turns out that a trigger was causing the problem.

Thanks guys