Why do these queries return differently?

Question

I am working on a query which I am updating a user defined table that always has 1 row (it's part of a much larger operation). This table is populated by querying from a larger employee table based on employee ID.

This query is working most of the time but in some scenarios I am getting a

'Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.'

error.

UPDATE @empInfo SET lastpromotiondate = ISNULL((
SELECT begindate
FROM performanceyear
WHERE lastpromotiondate BETWEEN 
 begindate AND enddate AND performanceyear.[fk_function_id] = 
 functionid AND isfederal = 
 performanceyear.federal), lastpromotiondate)

I couldn't pinpoint exactly why, I tried populating the values using a select and it was only returning 1 result.

I was at a loss, so I tried below and it worked.

UPDATE @empInfo SET LastPromotionDate = ISNULL((
SELECT begindate
FROM performanceyear
WHERE (SELECT LastPromotionDate FROM @empinfo) BETWEEN begindate AND enddate 
AND [FK_Function_ID] = (SELECT FunctionID FROM @empinfo) 
AND Federal = (SELECT isFederal FROM @empinfo)
), LastPromotionDate)

They definitely don't execute the same at all but I feel like I am missing something here. Why does one work and not the other? @empInfo only has 1 row.

Edit2:

I seem to have thought myself into a corner on this one!

The ORIGINAL query which was returning the error was:

UPDATE @empInfo SET LastPromotionDate = 
ISNULL((
SELECT begindate
FROM performanceyear
WHERE LastPromotionDate BETWEEN begindate AND enddate 
AND performanceyear.[FK_Function_ID] = FunctionID 
AND Federal = performanceyear.Federal)
,LastPromotionDate)

@empInfo had a 'Federal' column and so did 'performanceyear' when I looked into the execution plan of this one, it was comparing 'performanceyear'.Federal to 'performanceyear'.Federal. So I changed the @empInfo Federal name it isFederal and it worked.

The reason my other test was causing an issue was not because of 2 rows in @empinfo. It was because I was running update and then update again. The first update caused the second one to return 2 results from performanceyear.

Run independently they work.

With that all said, I still don't know of a solution which would work without the columns being different names since I can't use an alias on @empInfo in the update subquery.

Error seems pretty clear. One of your subquery is returning more than 1 value and you can not compare a column to a single value. Are you sure @empinfo only has 1 row - error suggests otherwise. — Kamil Gosciminski, Apr 28 '15 at 13:07
I am 100% sure it's only 1 row, I double checked with a select right before the updates and it shows 1 row of data. — Bryan Mathews, Apr 28 '15 at 13:12
Maybe there are some `INSERT` triggers that throw exception? — Evaldas Buinauskas, Apr 28 '15 at 13:13
I am literally declaring the empinfo table 2 lines before this. Then I insert into empinfo and then run an update. — Bryan Mathews, Apr 28 '15 at 13:18
But how many rows are in this table variable? There is obviously more than 1 or you wouldn't get this error. — Sean Lange, Apr 28 '15 at 13:19

score 0 · Accepted Answer · answered Apr 28 '15 at 13:25

I suspect you have some duplicate column names between @empInfo and performanceyear and because you are just selecting data from one table, there is no warning. If you use tablename.columnname, you might fix your first query.

I would rewrite your query as follows (untested)

UPDATE ei 
    SET LastPromotionDate = ISNULL(py.begindate, py.LastPromotionDate)
FROM performanceyear py
INNER JOIN @empInfo ei
    ON ei.LastPromotionDate BETWEEN py.begindate AND py.enddate
        AND ei.FunctionID = py.[FK_Function_ID]
        AND ei.isFederal = py.Federal

Just as I am writing my update, this is what the issue was, I had Federal twice in the two. — Bryan Mathews, Apr 28 '15 at 13:35

Why do these queries return differently?

1 Answers1