SQL ALTER Vs UPDATE Statement

SQL's altering commands are ALTER and UPDATE.

ALTER is used to change the database's relational structure (Tables).

The UPDATE command is used to update data in a database relation.

SQL ALTER Statement

The ALTER command is a Data Definition Language command.

This command changes the structure of an already-existing relation in the database.

You can add columns, delete or drop columns, rename a column's name, resize columns, or change the data type of the columns of a table.

That already exists in the database when you modify the structure of a relation.

Example: The following statement contains the Employee database with a Age column.

ID	Name	Dept
1	padmavati	Database
2	Siva Kumar	Webdesign
3	Harish Karthik	Database
4	Nirmala	Administration

We add age and joning date of Employee to above table the query will be,

--sql-server,Mysql

ALTER TABLE Employee ADD(

           Age INT
           JoiningDate DATE );

Output: The below table shows the output of above query

ID	Name	Dept	Age	Joining Date
1	Padmavathi	Database	34	23-02-2017
2	Siva Kumar	Web Design	23	04-03-2018
3	Harish Karthik	Database	29	09-07-2018
4	Nirmala	Administration	30	10-05-2019

It is really try to modify the data type of a column in an existing table.

SQL UPDATE Statement

The UPDATE command is a Data Manipulation Language command . It changes the values of one or more tuples in a relation's attribute values.

The WHERE clause, when combined with the UPDATE command, aids in the selection of tuples whose attribute values need to be changed.

When used in conjunction with UPDATE, the SET clause specifies the attribute names to be adjusted as well as the values to be allocated to them.

Assigning NULL or DEFAULT as a value to the attribute is possible.

Example 1: let's look at Book table to change the record with use of update statement.

ID	Name	Price	Year	Domain
1	Pro Oracle SQL	125	2017	Performance
2	Microsoft SQL Server 2008	80	2017	Administration
3	Head First SQL	90	2018	Database
4	Oracle Cookbook	199.97	2019	Security

We change the domain name in which id=2 column with use of update statement

UPDATE Book SET Domain = â€˜JDBCâ€™ WHERE ID = 2;

Output: The result of abpve query is

ID	Name	Price	Year	Domain
1	Pro Oracle SQL	125	2017	Performance
2	Microsoft SQL Server 2008	80	2017	JDBC
3	Head First SQL	90	2018	Database
4	Oracle Cookbook	199.97	2019	Security

The WHERE clause in the preceding commands defines the tuple whose attribute value is to be changed .

The SET clause specifies the name of the attribute to be assigned to the specified tuple in a relation, as well as the value to be assigned to it.

SQL Difference Between ALTER And UPDATE Statement

Here, we will mentioned the contrasts in ALTER & UPDATE Statement

ALTER	UPDATE
ALTER is a Data Definition Language command (DDL).	A Data Manipulation Language is the UPDATE Command (DML).
The Alter command will function on the structure level rather than the data level.	On the data level, the update command will work.
The ALTER command is used to add, delete, and edit the attributes of the database's relations (tables).	UPDATE The update command is used to make changes to existing records in a database.
Because columns in a relation refer to that relation's attributes, the ALTER command only affects those columns or attributes.	The Update Command modifies the value of an attribute on a specified tuple in a relation.
When the ALTER command adds a new column or attribute to a relation, the value of that attribute is set to `NULL` for all tuples by default.	UPDATE The command assigns the provided values to the tuples.
This command modifies the structure of the table.	This command makes modifications to the table's data.
Example : Table structure, Table Name, SP, functions etc .	Example : Change data in the table in rows or in column etc .

All Tutorials