Post reply

Trying to update 500,000 rows but getting error "Subquery returned more than 1 value"

  • July 15, 2006 at 3:55 am

    #94483

    Hi,

     

    I am new to this forum and also new to SQL.

    I am trying to update 500,000 rows in one table using the following:

    UPDATE S_EVT_ACT

    SET ACTIVITY_UID = ROW_ID

    WHERE ACTIVITY_UID <> ROW_ID

    I get the error message "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, ....etc"

    Can anyone help me to do this?? This is quite urgent, Im at work and the test team are waiting for me!!

     

    Thanks

    Scott

  • July 16, 2006 at 3:56 pm

    #649439

    The posted query can't be the complete query, right?

     

    N 56°04'39.16"
    E 12°55'05.25"

  • July 17, 2006 at 3:32 am

    #649483

    Is ACTIVITY_UID  a GUID?

    It looks like you have duplicate ACTIVITY_UID so there is a confusion over which record you are trying to update because there is more than one record with the same ACTIVITY_UID. This can happen with GUIDs they can be duplicated, even when you don't expect them to be.

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • July 17, 2006 at 6:53 am

    #649503

    Is there a trigger on that table? Try disabling the trigger prior to running the script.

  • July 17, 2006 at 7:47 am

    #649513

    you can also try just removing the where clause. it looks like what you are trying to do is sync the actId and Row id anyway.  If the server doesnt have to process the not equals operator each row, you will get a faster batch.

  • July 18, 2006 at 2:39 am

    #649726

    Thanks for the replies, it was the full query! As someone mentioned I was just trying to sync the ACTIVITY_UID with the ROW_ID.

    The problem was that there were triggers on the table. After removing the triggers it completed.

     

    Thanks

    Scott

Viewing 6 posts - 1 through 5 (of 5 total)

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