SQL 2005 update query

Interesting question. Try this:

DECLARE @Sample TABLE
    (
      column1 int,
	  column2 int)

insert into @sample
select top(10000) 32, null
from sys.all_objects S cross join sys.all_objects T

update myTable
set column2 = 1
from 
(select top (1000) * 
from @sample
where column1 = 32 and column2 is null) as myTable;

update myTable
set column2 = 2
from 
(select top (2000) * 
from @sample
where column1 = 32 and column2 is null) as myTable;

update myTable
set column2 = 3
from 
(select top (2000) * 
from @sample
where column1 = 32 and column2 is null) as myTable;

select * from @sample

Select allOpen in new window

Thanks Phillip! Will this reference my original table "Records" ? I ask because I have other fields in this table and I am basically adding and populating this new field which I am calling column2. In reality I already have several columns and wanted to update this column2 in this fashion based off this column1 field. The way I am reading your code there, am I starting from scratch with that?

Yes, but how do you determine the "first 1000 rows", etc?  Is there a third column that has, say, a datetime stamp or an identity that can be used to suss that out?

In my code, don't use lines 1-9, and change all references to @sample to [Records].

I do have a unique ID field in the table if that is what you mean

thanks Phillip, do I need to create MyTable first then or should that also be my table "Records"?

Hi Scott, thanks! Do I need to create "cte_assign_row_nums" in the table first or does that create itself with the query provided? And does ID represent my pre-existing unique ID field I mentioned?

The cte is created within the query itself: the keyword "WITH" at the start "tells" SQL that a cte definition is coming.

Yes, the ID is the unique ID column you mentioned.  But you can order by any column you want, it doesn't have to be a unique id.

ahhh ok, and in the end this update applies to column2 in Records correct?

Yes, that's correct.  CTEs are great for doing those types of UPDATEs, since they "remember" which individual row the UPDATE needs made to.

excellent, thanks Scott! Do I need to include the other fields in Records with this query, or will it work just fine with what we have here?

You should only need to specify the relevant columns in the CTE, it should work fine.  If SQL needs another column, it will give you an error about it :-).

Thanks Scott :)!

Hey Scott, one other clarification on this solution. As you could imagine the records of value column1 = 32 are scattered all throughout the table among other values. In the Case section of the query 1, this query is basically only looking at my records column1 = 32 so row_num values are only being assigned to these reocrds correct? My other records are ignored in this query, correct?

Correct.  Other rows will be ignored, only those that meet the WHERE condition are considered.

Hey Scott, another question if you don't mind. So far this has worked thanks! But if I wanted to toss another criteria up there should it work as well? For example in your query we have column1 = 32 as the criteria. I assume it should work as well if I said WHERE column1 = 32 AND columnA = 0 this will apply to all my records who have both those criteria, correct? It should do the same thing we have already but look at those two criteria without anything unexpected beyond my way of seeing it here?

Yes, that should work.

thanks Scott, if you don't mind I want to keep this question open for a week or so until this process is finished in case we run into any trouble?

OK by me, although I would follow up with it even if it were closed :-).

thanks Scott, you're awesome!