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

mkdlmr
Starting Member

21 Posts

Posted - 2013-12-18 : 10:50:23

Hi All,

I have a simple update query that looks like this:

Update Table1 set midpointDate = (select dateadd(day,(datediff(day,startDate,endDate)/2),starteDate) from Table1) where Table1.RowID = Table1.RowID

I am receiving the following error: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

Yes, it is all contained in the same table.

Thanks,
Mark

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-18 : 10:53:05

try this:


Update t1 set 
       t1.midpointDate = (select dateadd(day,(datediff(day,startDate,endDate)/2),starteDate) 
                            from Table1 
                            where rowid = t1.rowid) 
from   Table1 t1

Be One with the Optimizer
TG

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-18 : 10:54:18

[code]
Update Table1
set midpointDate = dateadd(day,(datediff(day,startDate,endDate)/2),starteDate)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

mkdlmr
Starting Member

21 Posts

Posted - 2013-12-18 : 11:08:45

Awesome! Thanks guys!

Both ways work. Is there any reason why I would use one over the other?

Thanks,
Mark

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-18 : 11:14:52

use khtan's method. It is more efficient. My solution was to tweak your original solution just to make it work with minimal change which resulted in a correlated sub query. khtan's method is a straight update simply using the values in the row. The only reason to use the subquery is if you needed a value from a row other than the one being updated.

Be One with the Optimizer
TG

mkdlmr
Starting Member

21 Posts

Posted - 2013-12-18 : 11:18:11

Gotcha, thanks again! :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources