CODEWITHSUNDEEP
mariadb
altair00
altair00

Reputation: 174

change column type and convert the existing values from string to integer in mariadb

I have a table name employees

MariaDB [company]> describe employees;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employee_id    | char(10)    | NO   |     | NULL    |       |
| first_name     | varchar(20) | NO   |     | NULL    |       |
| last_name      | varchar(20) | NO   |     | NULL    |       |
| email          | varchar(60) | NO   |     | NULL    |       |
| phone_number   | char(14)    | NO   |     | NULL    |       |
| hire_date      | date        | NO   |     | NULL    |       |
| job_id         | int(11)     | NO   |     | NULL    |       |
| salary         | varchar(30) | NO   |     | NULL    |       |
| commission_pct | char(10)    | NO   |     | NULL    |       |
| manager_id     | char(10)    | NO   |     | NULL    |       |
| department_id  | char(10)    | NO   |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+

MariaDB [company]> select * from employees;
+-------------+-------------+-------------+--------------------+---------------+------------+--------+----------+----------------+------------+---------------+
| employee_id | first_name  | last_name   | email              | phone_number  | hire_date  | job_id | salary   | commission_pct | manager_id | department_id |
+-------------+-------------+-------------+--------------------+---------------+------------+--------+----------+----------------+------------+---------------+
| 100         | Steven      | King        | [email protected]    | 515.123.4567  | 2003-06-17 |      1 | 24000.00 | 0.00           | 0          | 90            |
| 101         | Neena       | Kochhar     | [email protected] | 515.123.4568  | 2005-09-21 |      2 | 17000.00 | 0.00           | 100        | 90            |
| 102         | Lex         | Wow         | [email protected]     | 515.123.4569  | 2001-01-13 |      2 | 17000.00 | 0.00           | 100        | 9             |
| 103         | Alexander   | Hunold      | [email protected]  | 590.423.4567  | 2006-01-03 |      3 | 9000.00  | 0.00           | 102        | 60            |
| 104         | Bruce       | Ernst       | [email protected]   | 590.423.4568  | 2007-05-21 |      3 | 6000.00  | 0.00           | 103        | 60            |
| 105         | David       | Austin      | [email protected]  | 590.423.4569  | 2005-06-25 |      3 | 4800.00  | 0.00           | 103        | 60            |
| 106         | Valli       | Pataballa   | [email protected] | 590.423.4560  | 2006-02-05 |      3 | 4800.00  | 0.00           | 103        | 60            |
| 107         | Diana       | Lorentz     | [email protected] | 590.423.5567  | 2007-02-07 |      3 | 4200.00  | 0.00           | 103        | 60            |
| 108         | Nancy       | Greenberg   | [email protected] | 515.124.4569  | 2002-08-17 |      4 | 12008.00 | 0.00           | 101        | 100           |
| 109         | Daniel      | Faviet      | [email protected]  | 515.124.4169  | 2002-08-16 |      5 | 9000.00  | 0.00           | 108        | 100           |
| 110         | John        | Chen        | [email protected]    | 515.124.4269  | 2005-09-28 |      5 | 8200.00  | 0.00           | 108        | 100           |
| 111         | Ismael      | Sciarra     | [email protected] | 515.124.4369  | 2005-09-30 |      5 | 7700.00  | 0.00           | 108        | 100           |
| 112         | Jose        | Urman       | [email protected]   | 515.124.4469  | 2006-03-07 |      5 | 7800.00  | 0.00           | 108        | 100           |
| 113         | Luis        | Popp        | [email protected]    | 515.124.4567  | 2007-12-07 |      5 | 6900.00  | 0.00           | 108        | 100           |
| 114         | Den         | Raphaely    | [email protected] | 515.127.4561  | 2002-12-07 |      6 | 11000.00 | 0.00           | 100        | 30            |
| 115         | Alexander   | Khoo        | [email protected]    | 515.127.4562  | 2003-05-18 |      7 | 3100.00  | 0.00           | 114        | 30            |
+-------------+-------------+-------------+--------------------+---------------+------------+--------+----------+----------------+------------+---------------+

I wanted to change the salary column from string to integer. So, I ran this command

MariaDB [company]> alter table employees modify column salary int;
ERROR 1292 (22007): Truncated incorrect INTEGER value: '24000.00'

As you can see it gave me truncation error. I found some previous questions where they showed how to use convert() and trim() but those actually didn't answer my question.

sql code and data can be found here https://0x0.st/oYoB.com_5zfu

Upvotes: 1

Views: 667

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562368

I tested this on MySQL and it worked fine. So it is apparently an issue only with MariaDB.

The problem is that a string like '24000.00' is not an integer. Integers don't have a decimal place. So in strict mode, the implicit type conversion fails.

I was able to work around this by running this update first:

update employees set salary = round(salary);

The column is still a string, but '24000.00' has been changed to '24000' (with no decimal point character or following digits).

Then you can alter the data type, and implicit type conversion to integer works:

alter table employees modify column salary int;

See demonstration using MariaDB 10.6:

https://dbfiddle.uk/V6LrEMKt

P.S.: You misspelled the column name "commission_pct" as "comission_pct" in your sample DDL, and I had to edit that to test. In the future, please use one of the db fiddle sites to share samples, because they will test your code.

Upvotes: 2

Related Questions