April 23, 2003 at 3:39 pm
What is the best way to update multiple records if I can't do it in a single Update statement? I need to re-order a column for a certainamount of rows, i.e. change 4,6,8,9 to 1,2,3,4 - What would be th ebest way to achieve this?
April 24, 2003 at 1:23 am
This should do it:-
declare @ctr int
set @ctr = 0
update tablename set colname=@ctr,@ctr=@ctr+1
April 24, 2003 at 5:16 am
Are the columns in order via a clustered index so that the order is currently 4,6,8,9
if so then the previous should handle otherwise i say is 8,6,4,9 8 will be 1, 6 will be 2 and so on with the above.
April 24, 2003 at 9:42 am
This is exactly my issue, the clustered index is on the identity column, but the column I am updating is not the identity column but it does have a non clustered index on it. I have already tried the suggested method, and it works with my limited amount of test data, but I am concerned with it eventually updating in a non-sequential order. Is there not a way to specifically order the information that the update handles?
April 24, 2003 at 10:08 am
I haven't had a chance to try this out, but changing the update statement to something like this should get the updates done in the correct order.:-
update tablename set colname=@ctr,@ctr=@ctr+1 from tablename(index=nonclustindexname)
April 24, 2003 at 10:24 am
Oh sweet, that makes sense. If that works that will be perfect. I'll post a message saying if it works or not. Thanks!
April 24, 2003 at 10:28 am
I never thought about the index hint ordering. I will say that is cool and does work nicely.
April 25, 2003 at 3:31 am
Just an added note after I had a chance to really look over the index hint. Compared to an ORDER BY clause it does not neccessarily match if the values are not unique in the index with the results you would get from an ORDER BY. The reason is the way the index references the Clustered Index, it will be ordered by the physical sort and reference in the Non-Clustered Index.
So while an Order By on ColX with ColY being a Clustered Index may produce
ColX ColY
4 1
4 2
4 3
5 4
5 6
5 7
The Index hint may cause this
ColX ColY
4 3
4 1
4 2
5 6
5 4
5 7
For testing the resultset I always wrap like so
BEGIN TRANSACTION
Update Query
Select Query
If matches what I expect I run the following in the same connection.
COMMIT TRANSACTION
if not
ROLLBACK TRANSACTON
April 25, 2003 at 11:52 am
I used the suggested solution and it worked great. The last post isn't actually a problem since the numbers being updated are unique.
April 28, 2003 at 4:16 pm
I have a variation to this issue, in that I need to go backwards through the items, i.e. start from the bottom and work my way up since results from the first item depend on the 2nd one and so on. Any ideas on this other than using a cursor?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply