Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

New Column Updates Expand / Collapse
Author
Message
Posted Wednesday, April 4, 2007 7:07 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 7, 2013 6:12 PM
Points: 135, Visits: 87
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/bFernando/2954.asp
Post #356122
Posted Sunday, May 20, 2007 11:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 ">=" .
Post #367526
Posted Monday, May 21, 2007 1:28 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, April 7, 2014 3:32 AM
Points: 670, Visits: 101
hehe, very nice solution - simple is beautiful
Post #367537
Posted Monday, May 21, 2007 1:37 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing 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.



Post #367540
Posted Monday, May 21, 2007 2:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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].

Post #367544
Posted Monday, May 21, 2007 2:27 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing 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?


Post #367551
Posted Monday, May 21, 2007 2:33 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 2:45 AM
Points: 21,397, Visits: 9,610

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.

Post #367552
Posted Monday, May 21, 2007 2:59 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing 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.



Post #367558
Posted Monday, May 21, 2007 6:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 26, 2014 7:28 AM
Points: 257, Visits: 902
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)
Post #367591
Posted Monday, May 21, 2007 6:58 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 2:45 AM
Points: 21,397, Visits: 9,610
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.
Post #367600
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse