subquery Q?

Question

Post reply

subquery Q?

SQL_path

SSCrazy

Points: 2810
More actions
April 26, 2012 at 3:20 pm

#259988

Hi i have 2 temp tables in my procedure #item and #sequence. I am trying to update column itemsequence in first table
from second table using update.. set ..clause,
but I get error like subquery returned more than 1 result and proc fails. What am I missing here?
thanks for your help.
table: item
Itemid- Itemsequence- Item- date
1 - - A - 4/1/2012 7:00:10
2 - - B - 4/1/2012 7:00:20
3 - - C - 4/1/2012 7:00:30
4 - - D - 4/1/2012 7:00:40
1 - - A - 4/2/2012 8:00:05
2 - - B - 4/2/2012 8:00:25
3 - - C - 4/2/2012 8:00:35
4 - - D - 4/2/2012 8:00:45
table : sequence
Itemid - ItemSequence
2 - 1
3 - 2
4 - 3

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

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

SQL_Nw (4/26/2012)
What am I missing here?

First, you missed providing the data in readily consumable format.

Second, you missed showing us your subqeury query.

Third a JOIN is in order here. U can update the Item table with a JOIN (which is kinda similar to the sub-query.

But without seeing ur query, i cant gurantee a tested result!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

And, last but not least, you are also missing the expected results based on the sample data.

SQL_path SSCrazy Points: 2810 More actions · Answer 3

SQL_Nw (4/26/2012)
Hi i have 2 temp tables in my procedure #item and #sequence. I am trying to update column itemsequence in first table
from second table using update.. set ..clause,
but I get error like subquery returned more than 1 result and proc fails. What am I missing here?
thanks for your help.table: item
Itemid- Itemsequence- Item- date
1 - - A - 4/1/2012 7:00:10
2 - - B - 4/1/2012 7:00:20
3 - - C - 4/1/2012 7:00:30
4 - - D - 4/1/2012 7:00:40
1 - - A - 4/2/2012 8:00:05
2 - - B - 4/2/2012 8:00:25
3 - - C - 4/2/2012 8:00:35
4 - - D - 4/2/2012 8:00:45
table : sequence
Itemid - ItemSequence
2 - 1
3 - 2
4 - 3

oh, sorry I dont have complete query with me at this moment, but here is the piece that I remember:

update #item

Set ItemSequence= case when Itemid in (select s.Itemid from #sequence s join #item i on s.Itemid=i.Itemid) then

(select s.itemsequence from #sequence s join #item i on s.Itemid=i.Itemid)

else null end

Steve Jones - SSC Editor SSC Guru Points: 738663 More actions · Answer 4

Flip this to a SELECT or run your subquery for a range of data and see where you return more than one row. The error you mention is exactly the problem. Your subquery returns multiple rows from the data. You can either find a way to eliminate the multiple rows, or change the data.

My suspicion is that you need to add something to this to limit the results:

select s.Itemid from #sequence s join #item i on s.Itemid=i.Itemid

That isn't limited by row just because it's in a subquery. All of the matches get returned.

vinu512 SSCoach Points: 15729 More actions · Answer 5

Try running the below mentioned query as a single query:

select s.Itemid from #sequence s join #item i on s.Itemid=i.Itemid

I think this must be returning more than one "Itemid". If that is the case then try using "Select 1" or "Select Distinct" or add some conditions using the Where Clause to limit the result set to a single Item Id.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

SQL_path SSCrazy Points: 2810 More actions · Answer 6

Yes, I think its missing one extra field, i will try this thanks