MySQL: Add trailing zero to each row

Question

I have a DB setup with two columns, ID and VAL.

ID is an incrementing integer, starting from 1 and increasing by 1 for each row. VAL is a simple string.

How can I update each row's ID to be 10 times greater? E.g.: ID 1, 2, 3 -> ID 10, 20, 30

Or is there a better method to adding a trailing zero to the end of every row in a column?

Does this answer your question? MySQL Auto increment primary key increases by 10 — Filburt
– Filburt, Commented Feb 1, 2021 at 21:51
That would be an ill-advised use of an auto incrementing primary key. — Strawberry
– Strawberry, Commented Feb 1, 2021 at 23:00
agreed with Strawberry; you should not impute any meaning to id values; if you need to order and reorder them, use a separate column for that — ysth
– ysth, Commented Feb 1, 2021 at 23:03

ysth · Accepted Answer · 2021-02-01 21:57:50Z

1

If you are trying to update the existing values, you simply need to do:

update mysterytablename set ID=ID*10 order by ID desc;

(The order by is only needed if ID is a primary or unique key, which presumably it is.)

answered Feb 1, 2021 at 21:57

ysth

99.1k6 gold badges126 silver badges220 bronze badges

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

1 Comment

I can't believe this actually works, so I tested it... and it does. Still seems like a bad idea though .