SQL to Alter Every Third Row

Warning - Old Content

This post is quite old, and it might not apply anymore, or maybe there's a better way to do the same thing nowadays. Take with a big grain of salt.

This was an interesting problem posed to me by a friend. How does one go about altering every third row in a table? As an entirely contrived example, assume the table has exactly 2 columns, name and value, of types varchar(255) and int respectively. Assume that the rows are named like so, probably due to automatic entry or the involvement of a MSCE. Our table might look like this:

**name** **value**
record_01 0
record_02 0
record_03 0
record_04 0
record_05 0
record_06 0
record_07 0

Now, to update every third item, we can do some fancy string manipulations due to the wealth of string functions available. My friend wanted to do this using some sort of loop, but that’s not the SQL way. Here’s the happy query to do it:

UPDATE bar SET value=1
  WHERE substring(name FROM char_length('record_')+1) % 3 = 0;  

A bit of explanation:

char_length(str) returns the string length of its argument, in this case ‘record_’, which we want to get rid of so we can compare the numbering. We add 1 to that length because we want to compare what is left; finally, we take the modulo over 3 to find every third row. Simple!