Difference Between ALTER and UPDATE Command in SQL

If you are learning SQL, it is important for you to understand the difference between the ALTER and UPDATE commands. These two commands might seem similar to you at first, but their functions are totally different. For using them in your database queries, you must be clear about what you want to change, the structure, or the data.

In this blog, you will learn about the ALTER and UPDATE commands in SQL and the differences between them. You will also learn about their functionalities, along with practical examples. So let’s get started!

Table of Contents:

• What is the ALTER Command in SQL?
• Syntax for the ALTER Command in SQL
  1. Adding a Column
  2. Dropping a Column
  3. Renaming a Column
  4. Modifying the Data Type
• Features of the ALTER Command in SQL
• What is the UPDATE Command in SQL?
  • Syntax for the UPDATE Command in SQL
• Features of the UPDATE Command
• Difference between ALTER and UPDATE Command in SQL
• Conclusion

What is the ALTER Command in SQL?

The ALTER Command in SQL is used when you want to change the design or structure of the table. You can use it to add a new column to the table, remove one of the already existing columns, or modify the information about the column, such as its name or type. It does not change the data inside the table; it only changes the way the table is set up.

Syntax for the ALTER Command in SQL

Here, we will discuss the syntax for the ALTER Command in SQL for adding, dropping, renaming columns, and modifying the data types of an existing column in a table.

1. Adding a Column

For adding a column to the existing table, the syntax is:

ALTER TABLE tableName
ADD columnName columnDefinition;

The SQL query for the above syntax is given below:

Code:

-- Step 1: First, you have to create the Employees table
CREATE TABLE Employees (
    ID INT,
    Name VARCHAR(100)
);

-- Step 2: Then you have to insert sample data into the table
INSERT INTO Employees (ID, Name)
VALUES (1, 'E1'),
       (2, 'E2');

-- Step 3: Then you have to alter the table to add a new column 'Department'
ALTER TABLE Employees
ADD Department VARCHAR(50);

-- Step 4: Now, view the updated table
SELECT * FROM Employees;

Output:

Explanation:

The above SQL query is used to create a table named Employees. After that, two rows are inserted and a new column is added to it named Department. This is done using the ALTER command in SQL.

Unlock Advanced SQL Skills Today!

Enroll now and transform your future!

Explore Program

2. Dropping a Column

For dropping a column from the existing table, the syntax is:

ALTER TABLE tableName
DROP COLUMN columnName;

The SQL query for the above syntax is given below:

Code:

-- Step 1: At first, you have to create a sample table named Employees
CREATE TABLE Employees (
    ID INT,
    Name VARCHAR(100),
    Department VARCHAR(50)
);

-- Step 2: Then you have to insert sample data
INSERT INTO Employees (ID, Name, Department)
VALUES (1, 'E1', 'HR'),
       (2, 'E2', 'Finance');

-- Step 3: Then you have to drop the 'Department' column using the ALTER command in SQL
ALTER TABLE Employees
DROP COLUMN Department;

-- Step 4: At last, view the updated table
SELECT * FROM Employees;

Output:

Explanation:

The above SQL query is used to create an Employees table consisting of three columns: ID, Name, and Department. It then adds two rows to the sample data. After that, it uses the ALTER command in SQL to remove the Department column from the table. At last, it displays the updated table, which only shows the two columns, ID and Name.

3. Renaming a Column

For renaming a column from the existing table, the syntax is:

ALTER TABLE tableName
RENAME COLUMN olderName TO newName;

The SQL query for the above syntax is given below:

Code:

-- Step 1: At first, you have to create a sample table named Employees
CREATE TABLE Employees (
    ID INT,
    EmpName VARCHAR(100)
);

-- Step 2: Then you have to insert sample data
INSERT INTO Employees (ID, EmpName)
VALUES (1, 'E1'),
       (2, 'E2');

-- Step 3: After that, you have to rename the column 'EmpName' to 'EmployeeName' using the ALTER command in SQL
ALTER TABLE Employees
RENAME COLUMN EmpName TO EmployeeName;

-- Step 4: At last, view the updated table
SELECT * FROM Employees;

Output:

Explanation:

The above SQL query shows how you can use the ALTER Command in SQL to rename a column. Here, the column name EmpName is changed to EmployeeName without affecting the data in the table.

4. Modifying the Data Type

For modifying the data type of an existing column in the table, the syntax is:

ALTER TABLE table_name
ALTER COLUMN column_name column_type;

The SQL query for the above syntax is given below:

Code:

-- Step 1: At first, you have to create a sample table named Employees
CREATE TABLE Employees (
    ID INT,
    Salary INT
);

-- Step 2: Then you have to insert sample data
INSERT INTO Employees (ID, Salary)
VALUES (1, 30000),
       (2, 45000);

-- Step 3: After that you have to change the 'Salary' column to change its data type to VARCHAR
ALTER TABLE Employees
MODIFY COLUMN Salary  VARCHAR(20);

-- Step 4: View the updated table
SELECT * FROM Employees;

Output:

Explanation:

In the above example, the Salary column was of type INT. It is then changed to VARCHAR, which allows you to store the values as text if it is needed.

Features of the ALTER Command in SQL

1. The ALTER only works on the structure of a table, not with the actual data inside it.

2. You can use it when you want to add, remove, or change columns in a table.

3. When you add a new column using the ALTER Command, the values are automatically set to NULL. This is because you have not entered any data. 

What is the UPDATE Command in SQL?

You can use the UPDATE Command in SQL to change the data in a table. This includes updating values in existing columns. You cannot use the UPDATE command to change the entire structure of the table.

Syntax for the UPDATE Command in SQL

The syntax for the UPDATE command in SQL is given below:

UPDATE tableName        
SET column1 = value1, column2 = value2, ...,columnN=valueN -- columns updated with the new values
WHERE condition  -- condition to filter the rows to be updated.

The SQL query for the above syntax is given below:

Code:

-- Step 1: At first, you have to create a sample table named Employees
CREATE TABLE Employees (
    ID INT,
    Name VARCHAR(100),
    Department VARCHAR(50)
);

-- Step 2: Then you have to insert sample data
INSERT INTO Employees (ID, Name, Department)
VALUES (1, 'E1', 'HR'),
       (2, 'E2', 'Finance');

-- Step 3: Then you have to update the department of employee with ID 2
UPDATE Employees
SET Name = 'E2 Updated', Department = 'Marketing'
WHERE ID = 2;

-- Step 4: At last, view the updated table
SELECT * FROM Employees;

Output:

Explanation:

In the above example, the UPDATE command in SQL is used to change the Name and Department of the employee with ID 2. The structure of the table stays the same, only the data is modified.

Features of the UPDATE Command

1. The UPDATE command in SQL works on the data inside the table, and not its structure.

2. The UPDATE command is also used when you want to change the values of the existing records.

3. You can also set new values for specific columns in the chosen rows.

4. It can be helpful when you want to correct or modify data that is already stored in the table.

Difference between ALTER and UPDATE Command in SQL

The differences between the ALTER and UPDATE commands in SQL are given below in tabular format:

SQL Simplified: Learn for Free, Grow for Life

Master the language of data and start your journey toward a data-centric career!

Explore Program

Conclusion

It is important for you to understand the difference between the ALTER Command in SQL and the UPDATE Command in SQL for working with databases effectively. The ALTER Command in SQL helps you to change the structure of the table, like adding or removing columns, whereas the UPDATE Command in SQL is used to change the actual data that is stored in the table, like updating names or departments. Having a good understanding of when you should use which command will help you manage your tables better and avoid mistakes while you are working with SQL. To take your learning further, read the SQL interview questions and subscribe to the SQL Course by Intellipaat to upskill yourself.

Difference between Alter and Update Command in SQL – FAQs