[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

I need to update every twentieth row.

Posted on 2011-03-09
7
Medium Priority
?
230 Views
Last Modified: 2012-05-11
I need to update every twentieth row.

Is there a way in which I can do this without cursors?
0
Comment
Question by:Mr_Shaw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1200 total points
ID: 35084427
you need some "order by" to specify which row is the 20th ...
;with data as (
  select t.*, row_number() over (order by somekeycol) rn
    from yourtable t
)
UPDATE data
    SET x = abc
  WHERE rn % 20 = 0 

Open in new window

0
 
LVL 15

Accepted Solution

by:
derekkromm earned 400 total points
ID: 35084435
update t
set ?=?
from table t
inner join (select PK, row_number() over (order by PK) as 'rownum' from table) t1
on t.PK = t1.PK
where (t1.rownum-1) % 20 = 0
0
 
LVL 60

Expert Comment

by:HainKurt
ID: 35084562
or this:

update t
set myCol=@value
from table t
inner join (
  select PK from (
    select PK, row_number() over (order by PK) as rn from table
  ) x where rn % 20 =0
) t1
on t.PK = t1.PK
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Mr_Shaw
ID: 35084630
angelIII:

Hi Would, this be a random 1 in 20 or last 20% of rows?
0
 
LVL 60

Assisted Solution

by:HainKurt
HainKurt earned 400 total points
ID: 35084908
try:

update t
set myCol=@value
from table t
inner join (
  select PK from (
    select PK, row_number() over (order by newid()) as rn from mytable
  ) x where rn % 20 =0
) t1
on t.PK = t1.PK
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35084923
% is the modulo operator, nothing about random or last 20 rows ...
http://msdn.microsoft.com/en-us/library/aa276866%28v=sql.80%29.aspx
0
 

Author Closing Comment

by:Mr_Shaw
ID: 35085255
thanks
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

650 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