|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: 2 days ago @ 9:42 PM
Points: 135,
Visits: 82
|
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Sunday, March 14, 2010 10:55 PM
Points: 4,
Visits: 12
|
|
I think this could do the job... set nocount on SET ROWCOUNT 1000
declare @rowcount int, @batchcount int, @rowsupdated int declare @LastKey nvarchar(341) select @rowcount = 1,@batchcount = 0, @rowsupdated = 0, @LastKey = '' while @rowcount > 0 begin begin tran update MyTable set NewZip = ZipCode, @LastKey = NameKey where NewZip is NULL and NameKey >= @LastKey set @rowcount = @@rowcount select @rowsupdated = @rowsupdated + @rowcount if @rowcount > 0 select @batchcount = @batchcount + 1 commit end *If NameKey were unique, we could use ">" instead of ">=" .
|
|
|
|
|
Say Hey Kid
      
Group: General Forum Members
Last Login: Tuesday, December 18, 2012 4:26 AM
Points: 670,
Visits: 89
|
|
hehe, very nice solution - simple is beautiful
|
|
|
|
|
SSChasing Mays
      
Group: General Forum Members
Last Login: Thursday, July 22, 2010 9:06 AM
Points: 632,
Visits: 79
|
|
> update MyTable > set NewZip = ZipCode, > @LastKey = NameKey
That may well work in this example, but it is not something I'd like to use for production use. You are relying on @LastKey being set to the last (alphabetically) NameKey value updated. Your updating a set, it could take any of the values from that set.
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Sunday, March 14, 2010 10:55 PM
Points: 4,
Visits: 12
|
|
Ians, I believe you can use it for production. The last value assigned to @LastKey is the largest NameKey in the set because the update query definitely uses the index [LargeTable__IE1].
|
|
|
|
|
SSChasing Mays
      
Group: General Forum Members
Last Login: Thursday, July 22, 2010 9:06 AM
Points: 632,
Visits: 79
|
|
Can you point me at the documentation that guarantees that behaviour?
|
|
|
|
|
SSC-Insane
         
Group: General Forum Members
Last Login: Yesterday @ 7:02 PM
Points: 21,376,
Visits: 9,584
|
|
You are actually both right. But just to make sure of this behavior, you can use an index hint in the query. This pretty much guarantees the results.
|
|
|
|
|
SSChasing Mays
      
Group: General Forum Members
Last Login: Thursday, July 22, 2010 9:06 AM
Points: 632,
Visits: 79
|
|
We cant both be right.
From BOL:- Setting Variables and Columns Variable names can be used in UPDATE statements to show the old and new values affected. This should only be used when the UPDATE statement affects a single record; if the UPDATE statement affects multiple records, the variables only contain the values for one of the updated rows.
It just looks wrong to me, trying to set a single variable to many values from a table is a bad idea.
|
|
|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 204,
Visits: 705
|
|
Regardless of the "perfect" SQL for this task, 35 minutes is sufficiently better than 20 hours that the solution in the article is certainly good enough. If it generalizes better than an ideal but single-purpose query, then (imo) it has better reusability.
What is valuable in this article is that a problem is broken down and explained so we can see WHY the solution works so much better than the original example.
I would be interested in the performance/timing of setting the default value to a UDF at creation of the column? (which could be removed after the initial load)
|
|
|
|
|
SSC-Insane
         
Group: General Forum Members
Last Login: Yesterday @ 7:02 PM
Points: 21,376,
Visits: 9,584
|
|
| Yes you can both be right. The value will be set to the last row updated, however in a few rare cases the, update may be done in something that looks like a random order, using the index hint "should" aleviate that possibility.
|
|
|
|