0

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?

acgf
  • 136
  • 6
  • 2
    Does this answer your question? [MySQL Auto increment primary key increases by 10](https://stackoverflow.com/questions/35909120/mysql-auto-increment-primary-key-increases-by-10) – Filburt Feb 01 '21 at 21:51
  • 1
    There is no good reason for wanting to do this. None. – Strawberry Feb 01 '21 at 22:40
  • @Strawberry Inserting int vals between 10 and 20 is easier than inserting int vals between 1 and 2. Correct? – acgf Feb 01 '21 at 22:56
  • @Filburt No, but thank you. That taught me something new too. I don't need the primary key to increment by 10. I need space padding the existing primary keys to insert values between them. – acgf Feb 01 '21 at 22:59
  • 3
    That would be an ill-advised use of an auto incrementing primary key. – Strawberry Feb 01 '21 at 23:00
  • 2
    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 Feb 01 '21 at 23:03
  • Understood. In that case I should be sorting the table by the `VAL` column and then updating the auto-incrementing primary key to correspond with that order. – acgf Feb 01 '21 at 23:07
  • 1
    You should not be messing with the id – Strawberry Feb 01 '21 at 23:09

1 Answers1

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.)

ysth
  • 96,171
  • 6
  • 121
  • 214