Problem with UPDATE Using IN

Question

Problem with UPDATE Using IN

paully21

SSC Enthusiast

Points: 102
More actions
December 27, 2004 at 11:36 am

#74821

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'?)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 1

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

paully21 SSC Enthusiast Points: 102 More actions · Answer 2

Nope...Copied it right from Query Analyzer just like you see it

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 3

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

paully21 SSC Enthusiast Points: 102 More actions · Answer 4

Got it...

Colleague put a trigger on the table that was causing the error. Thanks for helping me!

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 5

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

>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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

paully21 SSC Enthusiast Points: 102 More actions · Answer 8

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.