December 27, 2004 at 11:36 am
I'm trying to issue the following statement...Note future statements may list many, many Batch_Ids. Batch_Id is an int column Processed is a bit column.
UPDATE tbl_Batch SET [Processed] = 1, Date_Processed = '12/27/2004 12:10:59 PM' WHERE Batch_Id IN (1,2)
I get the following results
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Any ideas would be appreciated. (I tried using Google, but have you ever tried searching for 'IN'?)
December 27, 2004 at 11:52 am
It looks like your query is correct. You are using the 'IN' keyword. Is it possible that the query you typed here is not the actual query that you tried?
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
December 27, 2004 at 11:54 am
Nope...Copied it right from Query Analyzer just like you see it
December 27, 2004 at 12:02 pm
Very strange. I have just created a table with the three columns you mention, populated it with some test data, and then ran your query. It ran fine.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
December 27, 2004 at 12:28 pm
Got it...
Colleague put a trigger on the table that was causing the error. Thanks for helping me!
December 27, 2004 at 12:38 pm
Those triggers can be a pain. Very powerful, but hidden from view! I knew there had to be a logical explanation.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
December 28, 2004 at 5:25 am
Just a note on very long IN lists.. If there are enough items in that IN clause you might encounter a stack overflow error. For very large lists, IN is inefficient - it's better in those cases to place the values in a table and join to that instead. (and you avoid possible errors)
/Kenneth
December 28, 2004 at 6:13 am
>Colleague put a trigger on the table that was causing the error.
Paully21,
There may be a much larger problem lurking in the shadows of that trigger you found... many folks write triggers to handle only a single row instead of everything that was inserted, updated, deleted. That really makes a mess of things for batch or set-based operations. The trigger should be examined to make sure that it is set-based and will handle more than one row. Post the trigger code, if you'd like.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 28, 2004 at 7:36 am
Yep...Totally the problem in fact. I changed it to account for multiple records and everything is fine.
Note to previous poster...By many Batch_Ids I meant like 20.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply