MySQL: Update every second row string

Question

I need to be able to update every even (2, 4, 6, etc) row string within the image column ... the name will end with an extension .png and I want to insert some text before the .jpg

So from MyImage.png to MyImage-Small.png

Then I need to be able to do the same for every odd (1, 3, 5, etc) row

Answer 1

Assuming the table has a numeric primary key column id, you can do this for even rows:

update mytable set picture = replace(picture, '.png', '-Small.png') where id % 2 = 0;

Similarly just change the predicate to id % 2 = 1 for odd rows

Answer 2

If the table has no numeric key which is a continuous sequence then we can still achieve selective updates for odd and even rows by using following update.

UPDATE mytable o
INNER JOIN
    (SELECT id, @row :=@row +1,mod(@row,2) AS num
        FROM mytable, (SELECT @row := 0) r)AS t
    ON o.id = t.id -- any indexed field which is unique for the table
  SET o.image =
        CASE num 
            WHEN 0 THEN 'even_row'
            WHEN 1 THEN 'odd_row'
        END;

All this query is doing is generating the sequence for the table then joining it with the original table so we update odd and even rows separately.