ALTER command in SQL
Using ALTER in SQL, I can update a table without having to create
another table and delete the old one. Organizations may have thousands
of tables, and knowing how to keep the data updated without risking data
losses is essential.
It’s important to note that there are a variety of ways to use ALTER—
including:
Adding new columns in a table
Renaming columns in a table
Modifying columns in a table
Dropping columns in a table
ADD command in ALTER
To add a new column using ALTER, we combine the ALTER command
with the ADD command, as seen below:
ALTER TABLE table_name ADD column_name datatype;
RENAME command in ALTER
Whenever I need to change a column name, I use
the RENAME command in ALTER as follows:
ALTER TABLE table_name RENAME COLUMN old_column_name
TO new_column_name;
MODIFY command in ALTER
Data-types define column properties and influence the quality of the data.
Updating a column’s datatype appropriately facilitates the aggregation
and filtering of the data by date using other SQL commands. Here’s how
we use MODIFY in SQL:
ALTER TABLE table_name ALTER COLUMN column_name datatype;
DROP command in ALTER
The last option that ALTER offers is to drop a column. There are
numerous reasons why I would need to DROP a column, which includes
but is not limited to:
Information that is no longer relevant,
Duplicated information, where the same column is found in another table,
Data quality issues,
Optimize storage space.
Dropping a column in SQL is easy, but use it cautiously because it is not
always possible to reverse a drop column action.
ALTER TABLE table_name DROP COLUMN column_name;
RENAME command in SQL
The RENAME command only applies to changing the table's name
and should not be confused with ALTER’s RENAME capabilities. In fact,
to change a table’s name, the query is simply:
RENAME TABLE old_table_name TO new_table_name;
TRUNCATE command in SQL
Truncating a table is not the same as deleting it. Truncating removes all
the data but keeps the table's structure intact, while deleting removes the
entire table, including its structure. Caution must be taken to choose the
proper command for the appropriate purpose.
The equivalent of TRUNCATE in Excel can be achieved by selecting all
the data under the column name, and press delete. In SQL, I write:
TRUNCATE TABLE table_name;
DROP command in SQL
Contrary to TRUNCATE, the DDL command DROP deletes the table
and all its values altogether. One must be careful when executing such
commands, as this action may not always be reversible.
The DROP command has a similar structure to the TRUNCATE query:
DROP TABLE table_name;