Categories

Netsparker Web Application Security Scanner

SQL UPDATE and DELETE

In the SQL server, the SQL DELETE & UPDATE keywords are more frequently used after 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 database to modify the records from specified Table_Name.
  • [SET ‘Column_Name’ = ‘New_value’] – ‘Column_Name’ are 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, means if column value is Full_Name  with data type varchar then you have to specify the values in the single quotes. Similarly for 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 WHERE clause.

Let’s go ahead & learn update query with practical example. In the Employee table each record have Phone & one Employee want to change the phone number in the employee record & second employee want to update the Full name. So let’s see how to update the Phone number using 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 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 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 SQL DELETE keyword?

As it name suggest that the DELELE keyword is used to delete the record(s) from database when they are no longer be used. Using 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 provigil cost data cannot be recovered, so prior executing this command you should make sure with where condition in the query is correct. To be on safer side, take the backups of 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 above case we have specified WHERE condition, so the records will be deleted from table based on the criteria you specified.

You can also use the following command to delete all the data from table, so only difference is don’t specifiy 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 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 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 a 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 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 employee table will be removed.

Summary

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

1 comment to SQL UPDATE and DELETE

  • Latashia Booe

    I truly appreciate the information you have on your site it seriously has helped me out a great deal thanks

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>