Updating and Deleting the Data

Introduction

In this tutorial, we shall look into the updating and deleting of the data in the tables. These commands are primarily necessary to apply the changes to the data in the rows of tables using SQL.

UPDATE Command

The update command is generally used to modify the data in the rows of a table. It can update the data in the specific rows of a table or all the rows of a table.

To perform the update of the data on the table, you must probably need the security privileges front the database. Before performing this action, ensure you are admin to update the data.

The syntax of the update command consists of the name of the table to be updated, the column name along with the new value to be updated and the filter condition to select the particular row.

Syntax

UPDATE Table_name
SET Column_name = ’Column_value’
WHERE Column_name = ’Specific_row_value’;

The syntax to the update the multiple column values will look similar with many column values to be updated.

Syntax

UPDATE Table_name
SET 
Column_name1=’Column_value1’
Column_name2=’Column_value2’
.
.
Column_nameN=’Column_valueN’
;

Using the update command one can update the different data values. But to set the data value to none we must use NULL Keyword. The NULL keyword will set the data value to empty. Now, let us look the syntax to understand the usage of NULL keyword.

Syntax

UPDATE Table_name
SET 
Column_name = NULL
WHERE Column_name=’Specific_row_value’
;

DELETE Command

The DELETE command is useful to delete the data in the tables. Similar to the UPDATE command DELETE command is quite easy and can delete data in specific rows or data in the complete table. 

Ensure to take care while using the DELETE command, because it sometimes might delete from all the rows, if not written the proper format. And you must have the security privileges from the client-server database to execute the DELETE operation in the SQL.

Let us look into the basic syntax to delete the single row from the table.

Syntax

DELETE FROM Table_name
WHERE Column_name=’Row_value’
;

DELETE command can remove the entire data in the rows but not the columns. To delete the columns exclusively, you can make use of the UPDATE command.

If you want to delete the entire table you can make use of the TRUNCATE command, which will be discussed in the upcoming tutorials.

Disclaimer

Truncate command and Drop table command are not similar, although physically mean the same. The truncate command will delete the data in the table but not the structure of the table, whereas drop command removes the entire data along with the structure of the table.

Guidelines to be taken care while executing DELETE and UPDATE commands

DELETE and UPDATE commands consists of the WHERE keyword in their syntax format. If at all, the WHERE keyword is not used it may result in delete or update of all the contents.

Before using the WHERE keyword, test it with the SELECT command and check whether the data is filtering properly. So that incorrect updates and deletions can be reduced.  

Ensure to perform these operations accurately because there will be no undo command to retrieve your data back.

This completes the end of this tutorial about updating and deleting the data in the tables using SQL. For any sort of doubts or the questions, you can reach out through the comment box.