January 10, 2008 at 11:48 am
Hello, I'm having some trouble with Sql Server (2000 I think).
I have a table that has been active for quite some time and has a varchar (length 255) field with image file names in it. Recently we had to add another field (varchar - length 150) to the table, and to populate the new column I'm trying to find a way to copy all the data from the first to the second. I've tried the code below but get the error that "the subquery returned more than one value". There's > 10,000 rows to the table, but i didn't think row quantity would matter as i'd tried the same code on a smaller table (fields were of type: text) and it worked like a charm.
UPDATE products SET Ref_Thumb = thumbnail
Ref_Thumb is the varchar with 150 length.
thumbnail is the varchar with 255 length.
Our development company is the on that created these tables, I have no idea why they used 2 different length varchars, or why they're so long to begin with since the data they're designed to hold wouldn't be more than 50 characters.
please help!
Thanks,
Mike
P.S. i'm also getting the same error when i try to use UPDATETEXT for more than a single row... does anyone know how to use UPDATETEXT to edit an entire column of data (separate project). I just need to remove a word out of a text field for a few thousand entries.
January 10, 2008 at 12:25 pm
You must have a trigger in place. Simply running that query as written should do what you want. Why you need to keep two copies of the same data I have no idea, but it should work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 10, 2008 at 5:11 pm
Thanks, I looked, and yes, I have 2 triggers on that table. Any ideas on disabling them while i run the update, and the re-enabling them somehow? or if i remove them, and just re-add them would that work?
January 11, 2008 at 5:39 am
Removing them and readding them will work. You could just run:
DISABLE TRIGGER ALL ON dbo.TableName
Just remember to run:
ENABLE TRIGGER ALL ON dbo.TableName
Otherwise, whatever the trigger does for you... it's not doing anymore.
BTW, from the sound of it you might want to examine the code within those triggers. One of them might not be dealing well with sets of data. That could be an issue down the road.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2008 at 11:19 am
Thanks for the help Grant, greatly appreciated. I've been trying to get this off my 'todo' list for a while.
Not sure why, but the code you gave didn't work, but it helped me more accurately research more on the net how to do it. (We're running SQL server 2000, i think your code only works in 2005?). anyhow, i ended using the following code:
ALTER TABLE products DISABLE TRIGGER ALL
UPDATE products SET Ref_Thumb = thumbnail
ALTER TABLE products ENABLE TRIGGER ALL
and it worked like a charm. Thanks!
-Mike
January 11, 2008 at 11:38 am
Oh, sorry. Yeah, I did post the 2005 code. This is the 2005 forum after all. Sorry about that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2008 at 1:05 pm
Haha, oups. My bad. Thanks for the help regardless though!
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply