What is the Difference Between ALTER and UPDATE in SQL

The main difference between ALTER and UPDATE in SQL is that ALTER is a data definition language command while UPDATE is a data manipulation language command.

Structured Query Language (SQL) is the language that allows performing operations on the data stored in RDBMS. SQL commands are mainly divided into three categories: Data Definition Language (DDL), Data Manipulation Language (DML) and Data Control Language (DCL). DDL commands help to modify the databases, tables etc. while DML commands help to manipulate data in RDBMS. DCL commands, on the other hand, help to define the users who can access the databases. In brief, ALTER is a DDL command while UPDATE is a DML command.

Key Areas Covered

1. What is ALTER in SQL
     – Definition, Functionality
2. What is UPDATE in SQL
     – Definition, Functionality
3. What is the Difference Between ALTER and UPDATE in SQL
     – Comparison of Key Differences

Key Terms

ALTER, DDL Command, DML Command, RDBMS, SQL, UPDATE

What is ALTER in SQL

“ALTER” is a DDL command that helps to modify the structure of the database or related objects such as tables. There are a number of uses in ALTER command.

The following statement adds a column called department to the employee table.

ALTER TABLE employee ADD(

            department VARCHAR(30)

);

The following statement adds multiple columns called department, address and date_of_birth to the employee table.

ALTER TABLE employee ADD(

            department VARCHAR(30),

            address VARCHAR(100),

            date_of_birth DATE

);

It is also possible to change the data type of an existing table of a column. Initially, the number of characters in the department field above was 30. However, the following statement changes the number of characters in the department field to 50 characters.

ALTER TABLE employee MODIFY(

            department VARCHAR(50)

);

We can also rename a column using ALTER. The following statement modifies the name of the column date_of_birth as dob.

ALTER TABLE employee RENAME

date_of_birth TO dob;

Furthermore, it is also possible to remove a column from a table. The following statement removes the column address from the employee table.

ALTER TABLE employee DROP (address);

What is UPDATE in SQL

“UPDATE” is a DML command that helps to change the records in a table.

For example, assume that the initial department of the employee 100 is IT. The following statement changes the department of that employee to HR.

UPDATE employee SET department = ‘HR’ WHERE emp_id =100;

Furthermore, we can change the values of multiple columns. The following statement changes the department to HR and address to Sydney of the employee 100.

UPDATE employee SET department = ‘HR’, address= ‘Sydney’ WHERE emp_id=100;

Difference Between ALTER and UPDATE in SQL

Definition

ALTER is a command in SQL that is used to modify the databases, tables etc. of an RDBMS while UPDATE is a command in SQL that is used to modify the rows in a table of an RDBMS. Thus, this is the fundamental difference between ALTER and UPDATE in SQL.

Category

The main difference between ALTER and UPDATE in SQL is that ALTER is a DDL command while UPDATE is a DML command.

Usage

The ALTER command helps to modify the structure of the database and related objects while the UPDATE command helps to modify the records in the table of a database. Hence, this is another difference between ALTER and UPDATE in SQL.

Conclusion

SQL is the language that allows performing operations on the data stored in RDBMS. “ALTER” and “UPDATE” are two such commands. The main difference between ALTER and UPDATE in SQL is that ALTER is a Data Definition Language command while UPDATE is a Data Manipulation Language command.

Reference:

1. “SQL: ALTER Command.” Types of Network Topology in Computer Networks | Studytonight, Available here.
2. “Using UPDATE SQL Command.” Types of Network Topology in Computer Networks | Studytonight, Available here.

Image Courtesy:

1. “1895779” (CC0) via Pixabay