If you want to change all columns of a certain type to another type, you can generate queries using a query like this:
select distinct concat['alter table ', table_name, ' modify ', column_name, ' ', if[is_nullable = 'NO', ' NOT ', ''], ' NULL;'] from information_schema.columns where table_schema = '' and column_type = '';For instance, if you want to change columns from tinyint[4] to bit[1], run it like this:
select distinct concat['alter table ', table_name, ' modify ', column_name, ' bit[1] ', if[is_nullable = 'NO', ' NOT ', ''], ' NULL;'] from information_schema.columns where table_schema = 'MyDatabase' and column_type = 'tinyint[4]';and get an output like this:
alter table table1 modify finished bit[1] NOT NULL; alter table table2 modify canItBeTrue bit[1] NOT NULL; alter table table3 modify canBeNull bit[1] NULL;!! Does not keep unique constraints, but should be easily fixed with another if-parameter to concat. I'll leave it up to the reader to implement that if needed..
This SQL tutorial explains how to use the SQL ALTER TABLE statement to add a column, modify a column, drop a column, rename a column or rename a table [with lots of clear, concise examples]. We've also added some practice exercises that you can try for yourself.
Description
The SQL ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The SQL ALTER TABLE statement is also used to rename a table.
Add column in table
Syntax
To add a column in a table, the ALTER TABLE syntax in SQL is:
ALTER TABLE table_name ADD column_name column_definition;Example
Let's look at a SQL ALTER TABLE example that adds a column.
For example:
ALTER TABLE supplier ADD supplier_name char[50];This SQL ALTER TABLE example will add a column called supplier_name to the supplier table.
Add multiple columns in table
Syntax
To add multiple columns to an existing table, the SQL ALTER TABLE syntax is:
ALTER TABLE table_name ADD [column_1 column_definition, column_2 column_definition, ... column_n column_definition];Example
Let's look at SQL ALTER TABLE example that adds more than one column.
For example:
ALTER TABLE supplier ADD [supplier_name char[50], city char[45]];This SQL ALTER TABLE example will add two columns, supplier_name as a char[50] field and city as a char[45] field to the supplier table.
Modify column in table
Syntax
To modify a column in an existing table, the SQL ALTER TABLE syntax is:
For Oracle, MySQL, MariaDB:
ALTER TABLE table_name MODIFY column_name column_type;For SQL Server:
ALTER TABLE table_name ALTER COLUMN column_name column_type;For PostgreSQL:
ALTER TABLE table_name ALTER COLUMN column_name TYPE column_definition;Example
Let's look at an example of how to modify a column called supplier_name using the ALTER TABLE statement. Note that most databases have a slightly different syntax.
For Oracle:
ALTER TABLE supplier MODIFY supplier_name char[100] NOT NULL;For MySQL and MariaDB:
ALTER TABLE supplier MODIFY supplier_name VARCHAR[100] NOT NULL;For SQL Server:
ALTER TABLE supplier ALTER COLUMN supplier_name VARCHAR[100] NOT NULL;For PostgreSQL:
ALTER TABLE supplier ALTER COLUMN supplier_name TYPE CHAR[100], ALTER COLUMN supplier_name SET NOT NULL;Modify multiple columns in table
Syntax
To modify multiple columns in an existing table, the SQL ALTER TABLE syntax is:
For Oracle:
ALTER TABLE table_name MODIFY [column_1 column_type, column_2 column_type, ... column_n column_type];For MySQL and MariaDB:
ALTER TABLE table_name MODIFY column_1 column_definition [ FIRST | AFTER column_name ], MODIFY column_2 column_definition [ FIRST | AFTER column_name ], ... ;For PostgreSQL:
ALTER TABLE table_name ALTER COLUMN column_name TYPE column_definition, ALTER COLUMN column_name TYPE column_definition, ... ;Example
Let's look at an example that uses the ALTER TABLE statement to modify more than one column. In this example, we will modify two columns called supplier_name and city.
For Oracle:
ALTER TABLE supplier MODIFY [supplier_name char[100] NOT NULL, city char[75]];For MySQL and MariaDB:
ALTER TABLE supplier MODIFY supplier_name VARCHAR[100] NOT NULL, MODIFY city VARCHAR[75];For PostgreSQL:
Drop column in table
Syntax
To drop a column in an existing table, the SQL ALTER TABLE syntax is:
ALTER TABLE table_name DROP COLUMN column_name;Example
Let's look at an example that drops [ie: deletes] a column from a table.
For example:
ALTER TABLE supplier DROP COLUMN supplier_name;This SQL ALTER TABLE example will drop the column called supplier_name from the table called supplier.
Rename column in table
Syntax
To rename a column in an existing table, the SQL ALTER TABLE syntax is:
For Oracle and PostgreSQL:
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;For SQL Server [using the stored procedure called sp_rename]:
sp_rename 'table_name.old_column', 'new_name', 'COLUMN';For MySQL and MariaDB:
ALTER TABLE table_name CHANGE COLUMN old_name TO new_name;Example
Let's look at an example that renames a column in the supplier table from supplier_name to sname.
For Oracle [9i Rel2 and up] and PostgreSQL:
ALTER TABLE supplier RENAME COLUMN supplier_name TO sname;For SQL Server [using the stored procedure called sp_rename]:
sp_rename 'supplier.supplier_name', 'sname', 'COLUMN';For MySQL and MariaDB:
ALTER TABLE supplier CHANGE COLUMN supplier_name sname VARCHAR[100];In MySQL and MariaDB, you must specify the data type of the column when you rename it.
Rename table
Syntax
To rename a table, the SQL ALTER TABLE syntax is:
For Oracle, MySQL, MariaDB, PostgreSQL and SQLite:
ALTER TABLE table_name RENAME TO new_table_name;For SQL Server [using the stored procedure called sp_rename]:
Example
Let's look at an example that renames a table called supplier to the new name vendor.
For Oracle, MySQL, MariaDB, PostgreSQL and SQLite:
ALTER TABLE supplier RENAME TO vendor;For SQL Server [using the stored procedure called sp_rename]:
sp_rename 'supplier', 'vendor';Practice Exercise #1:
Based on the departments table below, rename the departments table to depts.
CREATE TABLE departments [ department_id int NOT NULL, department_name char[50] NOT NULL, CONSTRAINT departments_pk PRIMARY KEY [department_id] ];Solution for Practice Exercise #1:
The following SQL ALTER TABLE statement would rename the departments table to depts:
ALTER TABLE departments RENAME TO depts;Practice Exercise #2:
Based on the employees table below, add a column called salary that is an int datatype.
CREATE TABLE employees [ employee_number int NOT NULL, employee_name char[50] NOT NULL, department_id int, CONSTRAINT employees_pk PRIMARY KEY [employee_number] ];Solution for Practice Exercise #2:
The following SQL ALTER TABLE statement would add a salary column to the employees table:
ALTER TABLE employees ADD salary int;Practice Exercise #3:
Based on the customers table below, add two columns - one column called contact_name that is a char[50] datatype and one column called last_contacted that is a date datatype.
CREATE TABLE customers [ customer_id int NOT NULL, customer_name char[50] NOT NULL, address char[50], city char[50], state char[25], zip_code char[10], CONSTRAINT customers_pk PRIMARY KEY [customer_id] ];Solution for Practice Exercise #3:
The following SQL ALTER TABLE statement would add the contact_name and last_contacted columns to the customers table:
ALTER TABLE customers ADD [contact_name char[50], last_contacted date];Practice Exercise #4:
Based on the employees table below, change the employee_name column to a char[75] datatype.
CREATE TABLE employees [ employee_number int NOT NULL, employee_name char[50] NOT NULL, department_id int, CONSTRAINT employees_pk PRIMARY KEY [employee_number] ];Solution for Practice Exercise #4:
The following SQL ALTER TABLE statement would change the datatype for the employee_name column to char[75]:
ALTER TABLE employees MODIFY employee_name char[75];Practice Exercise #5:
Based on the customers table below, change the customer_name column to NOT allow null values and change the state column to a char[2] datatype.
CREATE TABLE customers [ customer_id int NOT NULL, customer_name char[50], address char[50], city char[50], state char[25], zip_code char[10], CONSTRAINT customers_pk PRIMARY KEY [customer_id] ];Solution for Practice Exercise #5:
The following SQL ALTER TABLE statement would modify the customer_name and state columns accordingly in the customers table:
ALTER TABLE customers MODIFY [customer_name char[50] NOT NULL, state char[2]];Practice Exercise #6:
Based on the employees table below, drop the salary column.
CREATE TABLE employees [ employee_number int NOT NULL, employee_name char[50] NOT NULL, department_id int, salary int, CONSTRAINT employees_pk PRIMARY KEY [employee_number] ];Solution for Practice Exercise #6:
The following SQL ALTER TABLE statement would drop the salary column from the employees table:
ALTER TABLE employees DROP COLUMN salary;Practice Exercise #7:
Based on the departments table below, rename the department_name column to dept_name.
CREATE TABLE departments [ department_id int NOT NULL, department_name char[50] NOT NULL, CONSTRAINT departments_pk PRIMARY KEY [department_id] ];Solution for Practice Exercise #7:
The following SQL ALTER TABLE statement would rename the department_name column to dept_name in the departments table:
ALTER TABLE departments RENAME COLUMN department_name to dept_name;