SQL UPDATE and DELETE

In the SQL server, the SQL DELETE & UPDATE keywords are more frequently used after the SQL SELECT keyword.

What is the UPDATE keyword?

The UPDATE command is used to modify the records in the table. Upon executing this command the record values are modified based on values passed in the query. Along with WHERE clause you can update the specific records from the table.

SQL UPDATE Syntax:
Let’s see the basic syntax of DELETE command:

UPDATE Table_Name
SET ‘Column_Name’ = ‘New_value’
[WHERE CONDITION];

So here we see what use of each object:

• [UPDATE Table_Name] – This command is used to tell the database to modify the records from specified Table_Name.
• [SET ‘Column_Name’ = ‘New_value’] – ‘Column_Name’ is the name the column for which you have to modify the value & ‘New_value’ are the actual value which you have to set. While setting up the new values you have to enter the new values based on the column’s data type, which means if the column value is Full_Name  with data type varchar then you have to specify the values in the single quotes. Similarly, for the date data type, you have to enter date values single quotes. Numeric values you have to specify the values without quotes.
• [WHERE CONDITION] – This Clause used to filter out the result data set based on “condition”. You can specify any condition using the WHERE clause.

Let’s go ahead & learn to update queries with practical examples. In the Employee table, each record has a Phone & one Employee wants to change the phone number in the employee record & the second employee wants to update the Full name. So let’s see how to update the Phone number using the UPDATE query.

SELECT * FROM Employee WHERE Employee_Id IN (1,2);

Employee_Id	Full_Name	Gender	Date_Of_Birth	Phone
1	Tanya Smith	Female	1982-09-30	9123456789
2	Janet Tampi	Female	1980-07-10	9876543210
3	Nilsen Phil	Male	NULL	9112233445
4	Nilsen Jones	Male	1983-09-17	9988776655
5	Pettersen Willams	Male	1980-07-12	9944552299
6	Angels David	Male	1981-02-10	9955566611

The Employee “Tanya Smith” wants to change phone number from “9123456789” to “9198657777” & “Janet Tampi” want to update the Full name to “Janet Tampi – Simson” & Date of birth to “1980-07-10”, so you can use the following update queries to modify the records.

UPDATE Employee
SET Phone = ‘9198657777’
WHERE Employee_Id  = 1;

UPDATE Employee
SET Full_Name = ‘Janet Tampi - Simson’, Date_Of_Birth = ‘1980-08-10’
WHERE Employee_Id  = 2;

Note: If you want to update the multiple values in the same condition then you can use the comma operator in the “SET” & pass the multiple values.

Executing the above script the values in the Employee table are updated based on the values specified in the update query.

SELECT * FROM Employee WHERE Employee_Id IN (1,2);

Employee_Id	Full_Name	Gender	Date_Of_Birth	Phone
1	Tanya Smith	Female	1982-09-30	9198657777
2	Janet Tampi – Simson	Female	1980-07-10	9876543210
3	Nilsen Phil	Male	NULL	9112233445
4	Nilsen Jones	Male	1983-09-17	9988776655
5	Pettersen Willams	Male	1980-07-12	9944552299
6	Angels David	Male	1981-02-10	9955566611

What is the SQL DELETE keyword?

As it name suggests that the DELETE keyword is used to delete the record(s) from the database when they are no longer be used. Using the DELETE keyword you can delete single or multiple records from the database. So this command is used more powerfully with WHERE keyword while removing the unnecessary records from the database.

Upon executing this command the deleted data cannot be recovered, so prior to executing this command you should make sure with where condition in the query is correct. To be on the safer side, take the backups of the database or tables from where you are executing the delete query, so it will help you to compare the records after executing the query.

SQL DELETE Syntax
Let’s see the basic syntax of DELETE command:

DELETE from Table_Name
[WHERE CONDITION];

In the above case, we have specified WHERE condition, so the records will be deleted from the table based on the criteria you specified.

You can also use the following command to delete all the data from the table, so the only difference is don’t specify the WHERE condition:

DELETE from Table_Name;

So here we see what use of each object:

• [DELETE from Table_Name] – This command is used to tell the database to delete the records from specified Table_Name.
• [WHERE CONDITION] – This Clause used to filter out the result data set based on “condition”. You can specify any condition using the WHERE clause. 

Let’s see upon executing following query on Employee table resulting following list:

SELECT * FROM Employee

Following is the result after executing the above SQL query:

Employee_Id	Full_Name	Gender	Date_Of_Birth	Phone
1	Tanya Smith	Female	1982-09-30	9123456789
2	Janet Tampi	Female	1980-07-10	9876543210
3	Nilsen Phil	Male	NULL	9112233445
4	Nilsen Jones	Male	1983-09-17	9988776655
5	Pettersen Willams	Male	1980-07-12	9944552299
6	Angels David	Male	1981-02-10	9955566611

Let take an example of Employee where the “Nilsen Jones” employee left the company and they want to remove the employee record from the database. Its Employee_Id is 4, so we can write down the delete query on Employee table where pass the Employee_Id in the WHERE condition to delete the specific employee record.

DELETE FROM Employee WHERE Employee_Id = 4;

Once we execute the above script then the “Nilsen Jones” record will be deleted from Employee list and Employee table will look like as follows:

Employee_Id	Full_Name	Gender	Date_Of_Birth	Phone
1	Tanya Smith	Female	1982-09-30	9123456789
2	Janet Tampi	Female	1980-07-10	9876543210
3	Nilsen Phil	Male	NULL	9112233445
5	Pettersen Willams	Male	1980-07-12	9944552299
6	Angels David	Male	1981-02-10	9955566611

If we check the employee id record 4, then this record is deleted from the Employee table & not returned in the result set. Also, keep in mind the column can be deleted using delete command, the entire row will be deleted.

If you want to delete multiple records from the table then this can be accomplished using IN wildcard. Only you have to provide the list of record which you want to delete in WHERE clause.

DELETE FROM Employee WHERE Employee_Id IN (3,5);

Once we execute the above query then the employee IDs 3 & 5 entries from the employee table will be removed.

Summary

• The UPDATE command is to modify the existing records in the database.
• To modify the limited records in the database you can use WHERE clause is used along with the UPDATE command.
• The DELETE command is used to delete the records in the database which are no longer required in the database.
• Once you delete the records then the same records cannot be recovered again, so make sure you have taken up backup prior to executing the DELETE command.
• To delete the limited records in the database you can use WHERE clause is used along with DELETE command.

---

⇓ Subscribe Us ⇓

---

If you are not regular reader of this website then highly recommends you to Sign up for our free email newsletter!! Sign up just providing your email address below:

Enter your email address:

 

Check email in your inbox for confirmation to get latest updates Software Testing for free.

  Happy Testing!!!