0

Is there a way of run an alter table command with cast on MySQL databases?

I know that is possible to do it with Postgresql and Sql servers, but I didn't find anything about it when searching the documentation..

My database is very large and I want to convert Decimal to BigInt, so I'm trying to do it with a single command and avoid workarounds..

  • If you want to avoid conversion errors - run, in the same transaction, UPDATE my_table SET my_value = CEIL (or FLOOR) (my_value); depending on your requirements. Not sure if column mods can be part of a transaction? – Vérace Dec 21 '17 at 17:23
1

You can do it using a single statement.

CREATE TABLE T (val decimal(18,3));
INSERT INTO T VALUES (1.25);
INSERT INTO T VALUES (12.526);
INSERT INTO T VALUES (1000.25);
SELECT val FROM T;
|      val |
| -------: |
|    1.250 |
|   12.526 |
| 1000.250 |
ALTER TABLE T MODIFY COLUMN val bigint;
SELECT * FROM T;
|  val |
| ---: |
|    1 |
|   13 |
| 1000 |

dbfiddle here

  • But, did you want the values to be rounded off?? – Rick James Dec 27 '17 at 0:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.