Skip and update every 'n' row

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  August 24, 2015 at 7:39 am

  #299939

  Hi,

  to assign grading system, i want to skip some rows and update based on that. For Example, 1st record assign A, 2nd record to B, 3rd record to C and 4th to D. Then it starts again 5th record to A, 6th to B, 7th to C and 8th to D.

  declare @a table (val varchar(12), val2 int, val3 varchar(10))

  insert into @a Values ('ch1', 1,'')

  insert into @a Values ('ch2', 2,'')

  insert into @a Values ('ch3', 3,'')

  insert into @a Values ('ch4', 4,'')

  insert into @a Values ('ch5', 5,'')

  insert into @a Values ('ch6', 6,'')

  insert into @a Values ('ch7', 7,'')

  insert into @a Values ('ch8', 8,'')

  insert into @a Values ('ch9', 9,'')

  insert into @a Values ('ch10', 10,'')

  insert into @a Values ('ch1', 11,'')

  insert into @a Values ('ch2', 12,'')

  insert into @a Values ('ch3', 13,'')

  insert into @a Values ('ch4', 14,'')

  insert into @a Values ('ch5', 15,'')

  insert into @a Values ('ch6', 16,'')

  insert into @a Values ('ch7', 17,'')

  insert into @a Values ('ch8', 18,'')

  insert into @a Values ('ch9', 19,'')

  insert into @a Values ('ch10', 20,'')

  Results like,

  val val2 val3

  ------------ ----------- ----------

  ch1 1 A

  ch2 2 B

  ch3 3 C

  ch4 4 D

  ch5 5 A

  ch6 6 B

  ch7 7 C

  ch8 8 D

  ch9 9 A

  ch10 10 B

  ch1 11 C

  ch2 12 D

  ch3 13 A

  ch4 14 B

  ch5 15 C

  ch6 16 D

  ch7 17 A

  ch8 18 B

  ch9 19 C

  ch10 20 D

  using MOD i could update 2nd and 4th set of records like,

  update @a set val3 ='D'

  where val2 % 4 =0

  update @a set val3 = 'B'

  where val2 % 2 =0 and val3 = ''

  any help is appreciated.

  Thanks and Regards,

  Ami

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  August 24, 2015 at 7:54 am

  #1821961

  select

  n,

  NewColumn = CASE n%4 WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' WHEN 0 THEN 'D' END

  from ( -- just some sample data

  select top (100) n = row_number() over(order by (select null))

  from syscolumns

  ) d

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden