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:
Happy Testing!!!