Problem with UPDATE Using IN

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

  • 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

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

  • 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

  • Got it...

     

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

  • 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

  • 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

  • >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)

  • 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