Solved

SQL 2005 update query

Posted on 2015-01-14
21
69 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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:
Scott Pletcher 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:Scott Pletcher
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
 
LVL 69

Expert Comment

by:Scott Pletcher
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:Scott Pletcher
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:Scott Pletcher
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:Scott Pletcher
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:Scott Pletcher
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
best counters for cpu high usage 3 33
query execution hang 5 32
Help creating a spatial object in SQL Server 4 23
How to use three values with DATEDIFF 3 26
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

832 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