how do you increment a field in mysql?

Question

Say, you got a table of 100 records. And field age contains a some integers. And you want all those integers to be incremented by 1.

or you got a textfield called name and a bunch of names in there. And you want all those names to be prefixed as Mr..

Is there a way to achieve this in one SQL command?

The alternative would be to compile a recordset of these 100 recs and going thru a loop and then running an individual update statement.

podiluska · Accepted Answer · 2012-11-01 20:45:38Z

4

Use the update command

update yourtable
set age=age +1 

update yourtable
set name = 'Mr. ' + name
where gender='M'

answered Nov 1, 2012 at 20:45

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

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

Comments

Matt Ball · Accepted Answer · 2012-11-01 20:55:47Z

2

UPDATE mytable SET age = age+1
UPDATE mytable SET name = CONCAT('Mr. ', name)

If MySQL is in ANSI mode – specifically, PIPES_AS_CONCAT, you can use 'Mr. ' || name instead.

edited Nov 1, 2012 at 20:55

answered Nov 1, 2012 at 20:46

Matt Ball

361k102 gold badges655 silver badges725 bronze badges

3 Comments

Average Joe Over a year ago

Hi Matt, Is the | symbol an equal alternative to the + symbol in Dan J's reply?

Matt Ball Over a year ago

For string concatenation, or for addition? AFAIK the two are never interchangeable in SQL.

Average Joe Over a year ago

that opens up all kinds of potentials for me. I guess I can use other SQL functions there such as replace - I mean in place of the concat that is. Right?

Collectives™ on Stack Overflow

how do you increment a field in mysql?

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related