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?
精彩评论