Improve company productivity with a Business Account.Sign Up

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

I need to update every twentieth row.

I need to update every twentieth row.

Is there a way in which I can do this without cursors?
0
Mr_Shaw
Asked:
Mr_Shaw
  • 2
  • 2
  • 2
  • +1
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
derekkrommCommented:
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
 
HainKurtSr. System AnalystCommented:
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
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
Mr_ShawAuthor Commented:
angelIII:

Hi Would, this be a random 1 in 20 or last 20% of rows?
0
 
HainKurtSr. System AnalystCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
% 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
 
Mr_ShawAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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