开发者

SSMS error: Subquery returned more than 1 value... but there is no subquery

The full error message is this one: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. Everything I can find on it just says "yeah, just use TOP 1 and it'll be fine," only I don't even have a subquery or a select statement.

Here's the query:

update f
set f.new_monthnumber = datediff(mm, dateadd(dd, dbo.ufn_GetDaysInMonth(a开发者_Python百科.new_opendate) -   16, a.new_opendate), f.new_submit_date) + 1
from FilteredAccount a 
    left outer join FilteredNew_Financials f on f.new_name = a.accountnumber
where f.new_monthnumber is null

ufn_GetDaysInMonth is a user-defined function that analyzes the date passed to it and does not query the database at all, and in any case I've confirmed that it is not the cause of the error by running the query without it.

Anyone know what the heck is going on here?


Given that to write this query I would have swapped the tables and used an inner join, in order to have "FilteredNew_Financials" in both UPDATE and FROM clauses, pheraps there is a 1-to-n cardinality between the two tables on the join condition... maybe you have some dirty data in tables? Are you sure "new_name" and "accountnumber" are primary and foreign keys (at least logically, if not structurally)?


I would have written the query slightly differenty:

update FilteredNew_Financials
set new_monthnumber = datediff(mm, dateadd(dd, dbo.ufn_GetDaysInMonth(a.new_opendate) -   16, a.new_opendate), f.new_submit_date) + 1
from FilteredAccount a 
    left outer join FilteredNew_Financials f on f.new_name = a.accountnumber
where f.new_monthnumber is null

Does that work?

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜