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

Just not an exact query I've done before ... don't really have to be an ass about it, man. That fact that you took the time to write that comment says something about you. Either help or take a hike. — dcolumbus, Sep 18 '13 at 23:07
How do you differentiate between "even" and "odd" rows? What is the name of the table? What are the names of the columns? (`CREATE TABLE` would be fine.) — ypercubeᵀᴹ, Sep 18 '13 at 23:26
http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html#function_mod you should specify how you know its even... or is it just like alternative row styling?? — zod, Sep 18 '13 at 23:41

score 14 · Accepted Answer · answered Sep 18 '13 at 23:21

14

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

answered Sep 18 '13 at 23:21

gerrytan

40,313
9
84
99

score 2 · Answer 2 · answered Sep 19 '13 at 10:16

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.

MySQL: Update every second row string

2 Answers2