Solved

SQL 2005 update query

Posted on 2015-01-14
21
66 Views
Last Modified: 2015-01-22
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
Comment
Question by:mrosier
  • 11
  • 7
  • 2
  • +1
21 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40549226
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
 

Author Comment

by:mrosier
ID: 40549241
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 40549243
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40549250
In my code, don't use lines 1-9, and change all references to @sample to [Records].
0
 

Author Comment

by:mrosier
ID: 40549251
I do have a unique ID field in the table if that is what you mean
0
 

Author Comment

by:mrosier
ID: 40549257
thanks Phillip, do I need to create MyTable first then or should that also be my table "Records"?
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 40549267
;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
 

Author Comment

by:mrosier
ID: 40549295
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40549312
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
 

Author Comment

by:mrosier
ID: 40549317
ahhh ok, and in the end this update applies to column2 in Records correct?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40549321
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
 

Author Comment

by:mrosier
ID: 40549326
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40549332
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
 

Author Comment

by:mrosier
ID: 40549346
Thanks Scott :)!
0
 

Author Comment

by:mrosier
ID: 40549910
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40549960
Correct.  Other rows will be ignored, only those that meet the WHERE condition are considered.
0
 

Author Comment

by:mrosier
ID: 40551989
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40552014
Yes, that should work.
0
 

Author Comment

by:mrosier
ID: 40552128
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
 
LVL 69

Expert Comment

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

Author Comment

by:mrosier
ID: 40552140
thanks Scott, you're awesome!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
c# code 19 61
SQL server 2008 SP4 29 35
how open sql server always on replica read_only 13 19
Group by and order by clause 28 36
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now