[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
Medium Priority
230 Views
I need to update every twentieth row.

Is there a way in which I can do this without cursors?
0
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
• 2
• 2
• 2
• +1

LVL 143

Assisted Solution

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
``````
0

LVL 15

Accepted Solution

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

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

Author Comment

ID: 35084630
angelIII:

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

LVL 60

Assisted Solution

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

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

ID: 35085255
thanks
0

## Featured Post

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.
###### Suggested Courses
Course of the Month12 days, 21 hours left to enroll