Update and Delete Query

Introduction

Update and Delete statements are used as data modification clauses in SQL. After the SQL Select keyword, the SQL delete & Update keywords are most frequently used in the SQL server. The UPDATE command is used to make changes to the existing table. The record values are adjusted based on the values given in the query when the UPDATE command is executed. Whereas, the DELETE statement eliminates one or more rows in a table permanently.

Recommended Topic, Coalesce in SQL

Demo Database

To understand every aspect of the update and delete keywords in detail, we will take an employee database on which we will take several examples to understand how we can update data and remove several rows by using UPDATE and DELETE queries. The table given below consists of several attributes such as information of an employee where EmployeeID is the key attribute, while other attributes are Name, Age, Address, and Salary.

EMPLOYEE_ID	NAME	AGE	ADDRESS	SALARY
1	Alok Kumar	33	Jaipur	170000
2	Vishal Singh	34	Kolkata	132000
3	Vinay Rathor	26	Chennai	450000
4	Akash Bhatnagar	28	Hyderabad	108000
5	Sumit Sharma	26	Bhopal	290000

Update Query

The UPDATE Query in SQL is used to update existing records in a table. If you use the WHERE clause with the update query, you will get the authority to update the rows you want, rather than all of them.

The UPDATE statement in SQL Server (Transact-SQL) is used to update existing records in a table in a SQL Server database. 

Depending on whether you're conducting a standard update or updating one table with data from another table, the UPDATE statement has three different syntaxes.

Syntax 1: The syntax to UPDATE statement when updating one table in SQL Server (Transact-SQL) is:

UPDATE tableName  SET coln1 = expression1,     coln2 = expression2,     ... [WHERE conditions];

Syntax 2: When updating one table with data from another table in SQL Server (Transact-SQL), the UPDATE command has the following syntax:

UPDATE table1 SET coln1 = (SELECT expression1               FROM table2               WHERE conditions) [WHERE conditions];

Syntax 3: When updating one table with data from another table, the SQL Server UPDATE command has the following syntax:

UPDATE table1 SET table1.coln = table2.expression1 FROM table1 INNER JOIN table2 ON (table1.coln1 = table2.coln1) [WHERE conditions];

Parameters or Arguments

column1, column2

           Includes the columns you wish to update

expression1, expression2

Coln1 and coln2 should be given new values. As a result, the value of expression1 would be allocated to coln1, coln2 to expression2, and so on.

WHERE conditions

It is an optional condition. It selects the rows where the WHERE condition is met and then updates the selected rows to execute the query. And if we use the update query leaving out the WHERE clause, then all the rows of the table will get affected.

Steps to execute Update query

To update data in a table, the steps are as follows.

• First, use the UPDATE clause to give the table name for which you wish to make changes.
• Second, change the value of the column you want to change. If you want to edit data in multiple columns, use a comma to separate each column = value pair (,).
• Third, use the WHERE clause to define which rows you wish to change. The WHERE clause is not required. If you don't include the WHERE clause, the entire table will be updated.
• After you execute the statement, the database engine displays a notification indicating the number of rows that are affected.

You can combine the N number of conditions using the AND or the OR operators.

Examples:

Update Attributes for a single Record

The following SQL statement updates the first employee (EmployeeID = 1) with new age and a new salary.

UPDATE Employees SET Age = 36 , SALARY = 240000 WHERE NAME = 'Alok Kumar';

Output: 

Employees ID	NAME	AGE	ADDRESS	SALARY
1	Alok Kumar	36	Jaipur	240000
2	Vishal Singh	34	Kolkata	132000
3	Vinay Rathor	26	Chennai	450000
4	Akash Bhatnagar	28	Hyderabad	108000
5	Sumit Sharma	26	Bhopal	290000

 

Update Multiple Records

The number of records that will be updated is determined by the WHERE clause.

For all entries where the age is "26," the following SQL query will update the Address to "Chennai":

UPDATE Employees SET ADDRESS='Chennai' WHERE AGE = 26 ;

Output:

Employees ID	NAME	AGE	ADDRESS	SALARY
1	Alok Kumar	36	Jaipur	240000
2	Vishal Singh	34	Kolkata	132000
3	Vinay Rathor	26	Chennai	450000
4	Akash Bhatnagar	28	Hyderabad	108000
5	Sumit Sharma	26	Chennai	290000

 

Update Warning

When updating records, be cautious. If the WHERE clause is omitted, ALL records will be updated!

UPDATE Employees SET ADDRESS='Chennai'

Output: 

Employees ID	NAME	AGE	ADDRESS	SALARY
1	Alok Kumar	33	Chennai	170000
2	Vishal Singh	34	Chennai	132000
3	Vinay Rathor	26	Chennai	450000
4	Akash Bhatnagar	28	Chennai	108000
5	Sumit Sharma	26	Chennai	290000

Delete Query 

To delete existing records from a table, use the SQL DELETE Query. If you use the WHERE clause with a DELETE query, you can delete only the rows you want, rather than all the records.

The DELETE statement permanently deletes one or more rows from a table.

The following is the basic syntax for a DELETE query with a WHERE clause.

DELETE FROM table_name WHERE [condition];

You can perform N number of conditions by using AND or OR operators.

Also, the full syntax for the DELETE statement in SQL Server (Transact-SQL) is:

DELETE [ TOP (top_value) [ PERCENT ] ] FROM table [WHERE conditions];

Parameters or Arguments

Table

          The table from which you want to remove records.

WHERE conditions

Optional. The requirements must be completed in order for records to be erased.

TOP (top_value)

Optional. If the top value is supplied, it will eliminate the top number of rows in the result set. TOP(10), for example, deletes the top 10 rows that satisfy the delete criterion.

PERCENT

Optional. The top rows are based on a top value percentage of the whole result set if PERCENT is supplied (as specified by the PERCENT value). TOP(10) PERCENT, for example, deletes the top 10% of records that match the delete criterion.

In the FROM clause, you first specify the name of the table from which the rows are to be deleted.

The following command, for example, will delete all rows from the target table:

DELETE FROM target_table;

The TOP clause is used to determine the number or percent of random rows that will be removed.

The following DELETE command, for example, deletes ten random entries from the target table:

DELETE TOP 10 FROM target_table;

We don't know which rows will be destroyed because the table keeps its rows in an undetermined sequence, but we do know that the total number of rows deleted will be 10.

Similarly, the following DELETE command can be used to delete the 10% of random rows:

DELETE TOP 10 PERCENT FROM target_table;

Third, you will rarely, if ever, remove all rows from a table, but simply one or a few rows. In this situation, the search condition must be specified in the WHERE clause to limit the number of rows eliminated.

The rows that result in the search condition being true will be removed.

The WHERE clause is not required. The DELETE command will erase all rows from the table if you skip it.

The TRUNCATE TABLE statement, which is more efficient than the DELETE statement, should be used to remove all rows from a large table.

Steps to execute Delete query

To delete one or more rows from a table,

• First, use the DELETE FROM clause to indicate the table name from which you wish to remove data.
• Second, you utilize the WHERE clause to determine which rows should be removed. The statement will remove all rows from the table if the WHERE clause is omitted.

Examples:

Write a query to remove the tuple from the Employees table where the Employees ID is 1. 

DELETE FROM Employees WHERE Employees_ID = 1 ;

Output: 

Employees ID	NAME	AGE	ADDRESS	SALARY
2	Vishal Singh	34	Kolkata	132000
3	Vinay Rathor	26	Chennai	450000
4	Akash Bhatnagar	28	Hyderabad	108000
5	Sumit Sharma	26	Bhopal	290000

Delete All Records

You can delete all rows in a table without removing the table. The table structure, attributes, and indexes will all be preserved.

DELETE FROM Employees;

Output: 

Employees ID	NAME	AGE	ADDRESS	SALARY

 

 

Also see, Tcl Commands in SQL and SQL EXCEPT

Frequently Asked Questions

1. Why don’t we need to list fields in the SQL Server DELETE command?
   We don't need to list fields in the SQL Server DELETE command because we are deleting the entire row from the table.
    
2. What happens when you forget to use the WHERE clause in the update query?
   Where clause is the optional requirement that must be completed in order for records to be erased or updated, but if we do not use the where clause, then the update will be done in every row.
    
3. What is the use of the TOP Clause?
   A TOP clause is used to determine the number or percent of random rows that will be removed.