SQL Alter Table MODIFY.
Changing Table Definitions with alter and modify
- Most RDBMS systems allow you to change the structure of a table with the ALTER TABLE command.
- Syntax for ALTER with MODIFY can varies from RDBMS to RDBMS, so check your system manuals for specifics.
- To modify the type of data in a column can sometimes have unexpected resultet. It is therefore important that you are sure of what you are doing.
- Yet here is the syntax and examples of how this is done:
Change the type for a Column:
To change the column type in an empty table is normally no problem, but if the table contains data the result will differ for each RDBMS.
ALTER TABLE table_name MODIFY column_name column_type;ALTER TABLE CUSTOMER MODIFY TELEPHONE_NUMBER INT;In Oracle: If the column in the table contains characters that can not be convert to new type (here INT) there will be NO conversion. Error message will be given.
In MySQL: If the column in the table contains characters that can not be convert to new type (here INT) there will be a conversion and the column data will be a default value (0). Error message will be given.
ALTER TABLE table_name ALTER COLUMN column_name column_type;ALTER TABLE CUSTOMER ALTER COLUMN TELEPHONE_NUMBER INT;In SQL server: If the column in the table contains characters that can not be convert to new type (here INT) there will be NO conversion. Error message will be given.
ALTER TABLE table_name ALTER COLUMN column_name
TYPE INTEGER [ USING column_name::column_type]; ALTER TABLE CUSTOMER ALTER COLUMN TELEPHONE_NUMBER
TYPE INTEGER USING TELEPHONE_NUMBER::INTEGER; In PostgreSQL: If the column in the table contains characters that can not be convert to new type (here INT) there will be NO conversion. Error message will be given.
In some cases you don't need the USING clause.
ALTER TABLE CUSTOMER ALTER COLUMN TELEPHONE_NUMBER
TYPE VARCHAR(20); 
Next in menu