Update database field with a variable+field_value

Question

I am on now a big project and need to update database from MySQL, i need to update a field with some (variable value + that field value). I can do with a static value.

Here is what i have:

UPDATE table SET field_name = concat("static_value", field_name)

What i need to achieve the static_value value should be dynamic, it will start from 1 and ends with the number of rows.

How can i do this? help me!!!

Looks like a false goal. For a properly organizsed database you'd never need a static enumeration. — Your Common Sense, May 31 '16 at 04:04
@YourCommonSense, basically i need a new filed in my old database and the values are like that. — Murad Hasan, May 31 '16 at 04:06
Then your new database design is wrong. And the goal, for which you want to add such a field, has another, a proper solution. — Your Common Sense, May 31 '16 at 04:06
Possible duplicate of [Update SQL with consecutive numbering](http://stackoverflow.com/questions/1167885/update-sql-with-consecutive-numbering) — Ani Menon, May 31 '16 at 04:09
@AniMenon, how can i implement it in my query?? I have to add numeric value increment and a string value(that field value). — Murad Hasan, May 31 '16 at 04:11
It is not the same, I need two things, One is increment and also the field value. I don't to increment the field value only. — Murad Hasan, May 31 '16 at 04:14
@AniMenon, I have a MySQL not SQL only, Can you help me with my query? — Murad Hasan, May 31 '16 at 04:15
Did you even open that link? The answer says : `SET @a:=0; UPDATE table SET field=@a:=@a+1 WHERE whatever='whatever' ORDER BY field2,field3 ` — Ani Menon, May 31 '16 at 04:19
@AniMenon, I got it, little different from that one. Now i got it, thanks. — Murad Hasan, May 31 '16 at 04:34

score -1 · Accepted Answer · answered May 31 '16 at 04:33

-1

I got my result. By setting a variable i can achieve what i need. Thanks to @AniMenon.

SET @a:=0;
UPDATE table SET `field_name` = concat(@a:=@a+1, `field_name`);

answered May 31 '16 at 04:33

Murad Hasan

9,565
2
21
42

Update database field with a variable+field_value

1 Answers1