[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 85
  • Last Modified:

SQL 2005 update query

Hello! I am trying to find out if I can perform a certain update to a table called Records. To keep it simple, let's say I have  columns called column1 and column2. I have 5000 records in this table where column1 = 32. For the first 1000 of these records I want to update column2 = 1. The next 2000 after that I want to update column2 = 2. And then the next 2000 column2 = 3. Can I accomplish this in any way using update queries? Thanks!!
0
mrosier
Asked:
mrosier
  • 11
  • 7
  • 2
  • +1
1 Solution
 
Phillip BurtonCommented:
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

Open in new window

0
 
mrosierAuthor Commented:
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?
0
 
Patrick MatthewsCommented:
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?
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
Phillip BurtonCommented:
In my code, don't use lines 1-9, and change all references to @sample to [Records].
0
 
mrosierAuthor Commented:
I do have a unique ID field in the table if that is what you mean
0
 
mrosierAuthor Commented:
thanks Phillip, do I need to create MyTable first then or should that also be my table "Records"?
0
 
Scott PletcherSenior DBACommented:
;WITH cte_assign_row_nums AS (
    SELECT ID, column2, ROW_NUMBER() OVER(ORDER BY ID) AS row_num
    FROM Records
    WHERE column1 = 32
)
UPDATE cte_assign_row_nums
SET column2 = CASE
    WHEN row_num BETWEEN       1 AND 1000  THEN  1
    WHEN row_num BETWEEN 1001 AND 3000  THEN  2
    ...
    END
0
 
mrosierAuthor Commented:
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?
0
 
Scott PletcherSenior DBACommented:
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.
0
 
mrosierAuthor Commented:
ahhh ok, and in the end this update applies to column2 in Records correct?
0
 
Scott PletcherSenior DBACommented:
Yes, that's correct.  CTEs are great for doing those types of UPDATEs, since they "remember" which individual row the UPDATE needs made to.
0
 
mrosierAuthor Commented:
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?
0
 
Scott PletcherSenior DBACommented:
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 :-).
0
 
mrosierAuthor Commented:
Thanks Scott :)!
0
 
mrosierAuthor Commented:
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?
0
 
Scott PletcherSenior DBACommented:
Correct.  Other rows will be ignored, only those that meet the WHERE condition are considered.
0
 
mrosierAuthor Commented:
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?
0
 
Scott PletcherSenior DBACommented:
Yes, that should work.
0
 
mrosierAuthor Commented:
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?
0
 
Scott PletcherSenior DBACommented:
OK by me, although I would follow up with it even if it were closed :-).
0
 
mrosierAuthor Commented:
thanks Scott, you're awesome!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 11
  • 7
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now