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

Question

Post reply

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

Scott Waldegrave

SSC Enthusiast

Points: 132
More actions
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

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

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

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

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

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 2

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]

Mark Sanders-210921 Valued Member Points: 63 More actions · Answer 3

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

Andrew Stanton Ten Centuries Points: 1115 More actions · Answer 4

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.

Scott Waldegrave SSC Enthusiast Points: 132 More actions · Answer 5

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