Post reply

Update Multiple Rows

  • June 17, 2003 at 11:42 am

    #159362

    I'm trying to update multiple rows with a single statement and keep getting the same error. Probably just a rookie mistake, so don't laugh too hard. When I run the following query:

    UPDATE MyTable SET MyField = 'beta' WHERE MyField = 'alpha'

    I get the following error message:

    Subquery returned more than 1 value. This is not permitted when the subquery follows =,!=, <, >, <+ or when the subquery is used as an expression. The statement has been terminated.

    Is this some kind of safety to prevent someone from accidentally updating multiple rows? If so, how do I get around it when I really do want to update multiple rows?

  • June 17, 2003 at 12:01 pm

    #461792

    Hi kbe,

    Is that the only UPDATE statement that is being run???

    Usually the 'Subquery returned....' error occurs when you are trying to do something like :

    UPDATE myTable set myField = 'beta' where myField = (Select myField from myTable where myField2 = 'alpha')

    and the subquery returns multiple values that can't be mapped back to the myField in the outer query....

    haven't seen this particular error in a normal UPDATE statement like the one you have specified....

  • June 17, 2003 at 12:06 pm

    #461793

    winash,

    Yes, this single statement is the only one being run. This really has me confused, everything I've read about SQL tells me that this should work. Could this be something specific to my particular product (Microsoft SQL Server 2000)?

  • June 17, 2003 at 12:11 pm

    #461797

    hmmmmm...I'm stuck with SQL 7.0 - and anyway I don't think this should be a problem specific to SQL 2K...

    Would changing the UPDATE statement to

    UPDATE myTable set myField = 'beta' where

    myField in ('Alpha')

    make any difference???

  • June 17, 2003 at 12:25 pm

    #461800

    I tried your last suggestion and received the exact same error message.

  • June 17, 2003 at 12:33 pm

    #461802

    If possible could you post the table structure and some sample data....this should help....

    and this might sound dumb but - are you executing only that single query and no other queries in the same query batch??

    i.e assuming you are using query analyser - there "might" be some other queries written somewhere "up there" in QA that might be getting executed....

  • June 18, 2003 at 12:24 am

    #461850

    Has the table triggers?

  • June 18, 2003 at 2:11 am

    #461860

    I think the last reply was right. There must be some other sql running that is triggered by that update. The trigger must be using an "=" which you it may be possible to change to an "In ()" so it can run on multiple records. I can't see how the sql you are trying to run could possibly bring up an error (except maybe if the field you are updating is a text field..perhaps...but that would be return a different error message I think).

  • June 18, 2003 at 5:50 am

    #461898

    I've seen this when triggers are used as well.

  • June 18, 2003 at 8:53 am

    #461928

    Your Update statement is fine. Don't bother with doing an IN as it will have no effect.

    There has to be other code being executed for you to be getting this error as your Update statement in itself will not raise it. Look for triggers on the table as was mentioned earlier.

    FYI - There is no security around multiple row updates. That's one of the best things about set-based SQL. You can update a lot of rows in one swoop.

  • June 18, 2003 at 11:04 am

    #461953

    This is likely a poorly written trigger. Is dml on this table normally done through an application or proc which instanciates a cursor? In that case, up to now, you would not have seen this error because only one row at a time would have been updated. When you try to update multiple rows at the same time in an ad hoc fashion, the trigger has a subquery that returns multiple values - thus the error. This trigger, if it exists, is the 'rookie mistake' not your update. If the trigger does not exist - you'll need to look deeper.

  • June 18, 2003 at 11:20 am

    #461961

    It is indeed a trigger that is causing the problems. As previously indicated, the trigger isn't handling the multiple row update properly and chokes when more than one value is returned. Thanks for all the help and suggestions!

  • June 18, 2003 at 11:37 am

    #461966

    Just had to reply since I just had this

    problem as well. I'm doing this:

    alter table tblname disable Trigger all

    To get by and then enabling it when update

    is done.

  • June 19, 2003 at 4:30 pm

    #462297

    You can also get the error when attempting to update a variable instead of a column ( note @MyField instead of MyField):

    UPDATE MyTable SET @MyField = 'beta' WHERE MyField = 'alpha'

Viewing 14 posts - 1 through 13 (of 13 total)

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