Update multiple rows in a column in MySQL

Question

I'm having a table like that:

number
1  
2  
3  
4  
5

I want to change value in row 1 from 1 to 8, I used

update tableA set number=8 where number=1

the resulting table looks like follows:

number
8
2 
3 
4  
5

So far I tried the query below to update multiple rows,

   update tableA set number=8 where number=1;
   update tableA set number=10 where number=2;
   update tableA set number=11 where number=4;

And this works fine but is it possible to reduce it to something simpler?

Are the new values the result of an expression, maybe the result of a query or are these values arbitrary ones? — VMai
– VMai, Commented Apr 6, 2014 at 13:31

Hamidreza · Accepted Answer · 2014-04-06 13:29:01Z

2

In your query you can do something like this:

update tableA set number = number + 7 where number in (1,4);
update tableA set number=10 where number=2;

answered Apr 6, 2014 at 13:29

Hamidreza

3,1581 gold badge20 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tacca · Accepted Answer · 2014-04-06 13:28:03Z

1

Sure it is, you can add the statement OR after the where to make multiple choices.

Like this:

update tableA set number=8 where number=1 OR number=2;

Likewise you can use the between:

update tableA set number=8 where number BETWEEN 1 AND 2;

Best regards.

answered Apr 6, 2014 at 13:28

Tacca

14914 bronze badges