Average Joe
Average Joe

Reputation: 4601

how do you increment a field in mysql?

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.

Upvotes: 0

Views: 44

Answers (2)

Matt Ball
Matt Ball

Reputation: 359776

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

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

Upvotes: 2

podiluska
podiluska

Reputation: 51494

Use the update command

update yourtable
set age=age +1 

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

Upvotes: 4

Related Questions