Subquery return 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

SQL Server 2005 Forums

Transact-SQL (2005)

Subquery return more than 1 value

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-01-14 : 15:59:08

Hello, I am trying to update a temp table.
Can you look the code please?
What's wrong

select @qry='update #temp
set oper=(select tr.id from loan l inner join trans tr
on l.id=tr.id
where tr.cd=44'

exec(@qry)

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-14 : 16:07:14

#temp is out of scope. Why do you need dynamic sql for this?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-14 : 16:08:09

You have more than one number of rows being returned from your subquery,

select tr.id from loan l inner join trans tr
on l.id=tr.id
where tr.cd=44

If it were to return you one value, your query would work ok. Also, You needn't have your query dynamic in this case. \

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-14 : 16:11:58

Your database table must have duplicate rows and the query is returning those.

If you are unsure that the table contains duplicate rows try modifying this query and run it against your table.

Note this query returns all duplicate rows of partnumbers for my Inventory system.

SELECT manufacturer,partnumber
FROM Inventory
WHERE partnumber
IN ( SELECT partnumber
FROM Inventory
GROUP BY partnumber
HAVING Count(*) >1
)
ORDER BY manufacturer

If you identify duplicate rows then you will have to come up with a business rule on how to handle them (delete or retain?).

Also has the others have posted you do not need dynamic SQL.

r&r

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-01-14 : 16:12:38

It is from a stored procedure so I used dynamic sql.
I want to update all rows.
How to modify the query?

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-14 : 16:14:32

It depends on how you want to handle duplicate rows..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-14 : 16:14:38

You should not blindly use dynamic SQL just because you are using a stored procedure. This is very bad. I have one system that has hundreds of stored procedures and there is absolutely no dynamic SQL code in any of them.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-01-14 : 16:17:24

Just ignore the dynamic SQL code and duplicate rows. I can discard it later on.
Now how to return all rows?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-14 : 16:18:54

This is probably close to what you want:

update t
set oper = tr.id
from #temp t
inner join loan l
on t.?? = l.??
inner join trans tr
on l.id=tr.id
where tr.cd=44

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-01-14 : 16:27:29

Well, if it is still from #temp t.
I worry it can't be updated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-14 : 16:38:28

I don't understand what you mean. That's as close of a solution as we can provide given the information that you have provided.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-01-14 : 16:45:19

Thanks.
It works out.