Subquery returned more than 1 value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Subquery returned more than 1 value

Author

Topic

josh2009
Yak Posting Veteran

81 Posts

Posted - 2009-06-04 : 14:13:04

Hi,

Trying to get back into the SQL Server world and am having difficulty with an update statement. Any help would be greatly appreciated. Here is my statement -

update invitemlocations
set on_hand = (select units_in_stock from items where items.ss_item_id = invitemlocations.ss_item_id)

where exists
(select units_in_stock from items where items.ss_item_id = invitemlocations.ss_item_id)

Obviously my subquery is returning more than one row for some items. I can use the TOP 1 in my select statement and it will process those items with only one row in the invitemlocations table. But I have a few hundred items with more than one row in the invitemlocations table and using the TOP 1 will update the on_hand column with the same value for all the rows with the same ss_item_id.

Thanks again for any help.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-04 : 14:29:34

How do you tell which item matches with which invitemlocation if there can be muliples with the same id? Maybe some sample data and expected output would help to clarify. This link will help you prepare: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-04 : 14:40:31

quote:
Obviously my subquery is returning more than one row for some items

Yes and this is not always a problem if you know what you are doing, but it is not clean.

quote:
I can use the TOP 1 in my select statement and it will process those items with only one row in the invitemlocations table

No! It will return ONE value for each row that fits to your WHERE-clause

quote:
and using the TOP 1 will update the on_hand column with the same value for all the rows with the same ss_item_id

Yes and that is why I said it is not clean...

First we should know by which conditions (do you have sample data?) should a value be retrieved to update the column on_hand.
Then we can try to build a query for that.

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-04 : 14:41:39

My fingers are toooo slow

and maybe my brain too...

No, you're never too old to Yak'n'Roll if you're too young to die.

josh2009
Yak Posting Veteran

81 Posts

Posted - 2009-06-04 : 16:33:05

Here is the data that I have been working with.

INVITEMLOCATIONS

ss_item_id ssroomid on_hand
----------- ----------- -----------
2922 2 NULL
2922 5 NULL
2922 6 NULL

ITEMS

ss_item_id units_in_stock ssroomid
----------- -------------- -----------
2922 229 5
2922 80 6
2922 48 2

Here is the update statement that I am having problems with. Thanks for any help.

update invitemlocations
set on_hand = (select units_in_stock from items where items.ss_item_id = invitemlocations.ss_item_id and items.ssroomid = invitemlocations.ssroomid)
where exists
(select units_in_stock from items where items.ss_item_id = invitemlocations.ss_item_id and items.ssroomid = invitemlocations.ssroomid)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-04 : 16:45:11

[code]DECLARE @INVITEMLOCATIONS TABLE (ss_item_id INT, ssroomid INT, on_hand INT)

INSERT @INVITEMLOCATIONS
SELECT 2922, 2, NULL
UNION ALL SELECT 2922, 5, NULL
UNION ALL SELECT 2922, 6, NULL

DECLARE @ITEMS TABLE (ss_item_id INT, units_in_stock INT, ssroomid INT)
INSERT @Items
SELECT 2922, 229, 5
UNION ALL SELECT 2922, 80, 6
UNION ALL SELECT 2922, 48, 2

UPDATE
invitemlocations
SET
on_hand = items.units_in_stock
FROM
@items AS items
INNER JOIN
@INVITEMLOCATIONS AS invitemlocations
ON items.ss_item_id = invitemlocations.ss_item_id
AND items.ssroomid = invitemlocations.ssroomid

SELECT *
FROM @invitemlocations[/code]

josh2009
Yak Posting Veteran

81 Posts

Posted - 2009-06-04 : 17:21:23

Hi Lamprey,

Thanks for the quick reply. That definitely worked for me. I really appreciate your help.

Victor

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources