MYSQL Replace number values

Question

I've got a table in my database with id's and I'm tryning to replace them with a new id's. Example:

|  id |
------
|  1  |
|  1  |
|  1  |
| 14  |
| 14  |
|  6  |
|  6  |
|  6  |

I want to replace all 1 with 25, 14 with 18, 6 with 15 etc. But when i run:

UPDATE your_table
SET your_field = REPLACE(your_field, '1', '25')

It changes all 1 to 25 and also 14 to 254. How to do this right?

Why do you want to replace id values from 1 to 25 and 14 to 18 etc..? and it is not best practice to have duplicate id's in your table which should be a primary key column.. — Lucky
– Lucky, Commented Apr 13, 2015 at 9:56
It's just an example. I want to replace old category id to a new one to all my posts — Daniel Koczuła
– Daniel Koczuła, Commented Apr 13, 2015 at 10:00

Tushar · Accepted Answer · 2015-04-13 10:06:57Z

1

You can use a where condition after update statement.

UPDATE your_table
SET your_field = REPLACE(your_field, '1', '25')
where id = 1

If your_field is id; Then you can use;

UPDATE your_table
SET id = REPLACE(id, '1', '25')
where id = 1

answered Apr 13, 2015 at 10:00

Tushar

3,6759 gold badges31 silver badges51 bronze badges

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

1 Comment

Don't you just want to "UPDATE your_table set id = 25 where id = 1"?