Limited 20% Scholarship - Use Coupon - JAN20

What is SQL Delete Query? How to Delete Duplicate (Records, Rows, Tables)

What is SQL Delete Query
  • »
  • SQL
  • »
  • What is SQL Delete Query? How to Delete Duplicate (Records, Rows, Tables)

A Delete Query in SQL is used to delete a set of records, duplicate rows, table etc. based on the criterion specified. This is one of the most fundamental features of RDBMS because you had the flexibility to delete multiple records together even when they are linked to different tables to make the data management easier and more flexible.

A depth understanding of SQL delete query in SQL will improve the performance of database apps and makes you a highly skilled resource for the IT Companies. Obviously, you can delete duplicate entries manually as well but the process is time-consuming and difficult to maintain.

What are the Features?

  • Empty the table i.e. delete the entire records from the table.
  • Delete a particular row or particular field from the table.
  • Deleting a set of values or duplicate data from the table.
  • Delete the records based on the criterion specified by developers.
  • Delete data for the linked tables based on conditions.

What is a Delete Query in SQL?

The objective of Delete command in SQL is to delete rows that are no longer needed in the database tables. It will delete the entire row from the table and very handy to manage the temporary or obsolete data in the database. This is possible to delete multiple rows in the database with a single query only. It is proven more advantageous when you need to delete multiple entries or duplicate rows from the table.

Once you will delete rows from the table, it cannot be recovered back. So, you are strongly recommended to take the data backup before you start making changes to the database tables. It will allow you to restore important data in the case of wrong use of the command that was actually important for the database application.

The syntax of Delete Command in SQL –

The basic syntax of the delete command in SQL is given as below –

DELETE FROM ‘table_name’ [WHERE condition];

The syntax with the “DELETE FROM” would explain to the server that something has to be deleted from the table. After the keyword, you need to add the table name and where condition in the end, if required. Where clause is optional in case of the SQL server and it is needed only when you wanted to restrict the delete operations based on the query. In case, you are not using the where clause then the entire table would be deleted. This is not possible to delete a particular column but you can delete the entire row from the table only.

Read:   How to Create Stored Procedure & Trigger in SQL Server

The full syntax for Delete command in SQL –

Till the time, we have discussed the basic syntax used by beginners. Here, is an advanced form of Delete query in SQL or the full syntax that may be needed for large database apps –

DELETE [TOP (top_value)] (PERCENT)]

FROM table [WHERE conditions];

Top (top-value) – This is an optional field in SQL that would delete the top values from the database based on the values. For example, if you will write TOP (10) then it will work on the first 10 values in the table only.

Percent – This is again an option field in the SQL where rows are deleted based on the percentage. For example, you can ask to delete only 5 percent of total rows within a database.

How to delete the duplicate rows/records in a database?

If you got a situation where you have to select the unique entries only from a particular database table then you have to be extra careful while fetching the records. So, how to write a syntax to fetch the unique values only not the duplicate entries?

To overcome the situation, you should use the DISTINCT keyword along with the Select command in the SQL. Here, is how should you write the syntax to get your job done accurately-

SELECT DISTINCT column1, column2, ……..columnN

FROM table_name

WHERE [conditions]

This syntax will automatically delete the duplicate records and return unique values as needed.

How to delete a Table in SQL?

To delete an entire table in the SQL, you should use the following command as given below –

Read:   SQL Developer Resume Template Sample – Complete Guide for Fresher

DELETE FROM table_name;

You just have to give the table name that you want to delete. In case, you are interested in deleting a particular row only then use the following command –

DELETE FROM table_name [WHERE condition];

With the Where clause, you can quickly specify the conditions to restrict the delete operations. There are more similar operations like DELETE that include DROP and TRUNCATE but they are not necessarily the same and different from each other in possible ways.

For example, the Delete command in SQL only removes the database entries but it will not free up the space taken by the server. So, if you wanted to delete the table schema or properties as well then you should use Truncate command in this case. So, the truncate command in SQL is not only used to delete the rows but it will free the containing space too. Here is the syntax for the Truncate command in the SQL –

TRUNCATE TABLE employee;

Next is DROP command in SQL that will drop the table definition along with delete operation and all the relationships that are existing with the table are no more valid. So, what are the feature of DROP query in the SQL?

  • It will drop the structure of the table.
  • It will drop the relationships linked to the table.
  • It will drop the integrity constraints valid on the table.
  • The permission sets will be changes as soon as DROP command is performed on the database table in the SQL.

In the case of Truncate command in SQL, it will not delete the table structure, so you will not face the problems as given above. Based on the above discussion, you can quickly decide which command suits your database operations the most.

How to delete a record from the table?

Deleting a single entry from the table is easy with the right command and the process. To delete a particular record from the table, the entry should look like this –

Read:   SQL Server Recovery Models-Simple, Full and Bulk Log

DELETE FROM student_name

WHERE id = 003;

Based on the syntax given above, it will delete the entire row with the ID 003 and output will be displayed as needed. Sometimes, the Delete query fails to run in the database if special permissions are set by the administrators.

Here, you need to check first if you are permitted to make the changes in the table or not. Or you should log in as the administrator to make the required changes. If the database is set to the READ-ONLY mode then it cannot be modified. If the database is linked then it is not allowed to modify.

If the table does not have a primary key then you are restricted to make the changes in the database table. Further, this is necessary learn for you what is trivial data or historical data in SQL and how should you handle it?

Avoid Deleting Historic data in the SQL

This is a different scenario deleting trivial data from some temporary table and deleting historic data is a different aspect. You should know the difference between while working on two scenarios. Most of the times people consider deleting historic data that it will never be used again. In case, you needed it later then it could be expensive and impossible sometimes.

Here, in this situation, two solutions are possible.

  • You should tag the historic data instead of deleting it completely.
  • The best idea is storing historical data in an archived file.

Conclusion:

Make sure that you spend enough time in learning and understanding the Delete command in the SQL and its different operations like how to delete a table, how to delete duplicate rows in the SQL, how to delete rows in the SQL etc. With this article, you can save a lot of manual efforts and there is no need of writing unnecessary codes too. Keep in mind that with delete command, you are just hiding the record not deleting them completely. For permanent delete, this is always better using DROP or TRUNCATE commands.

JanBask Training

JanBask Training

JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.



Write a Comment

avatar
  Subscribe  
Notify of

Trending

Top 30 Core Java Interview Questions and Answers for Fresher, Experienced Developer
Top 20 AWS Interview Question and Answers For Fresher, Experienced Developer
Top 30 Manual Testing Interview Questions & Answers for Fresher
Spring MVC Interview Questions and Answers
Top 30 Frequently asked Selenium Interview Questions and Answers

Related Posts

Top 50 SAS Interview Questions and Answers For Fresher, Experienced
How to Clear SQL Server Transaction Log File with DBCC Shrinkfile
What is Update Query in SQL? How to Update (Column Name, Table, Statement, Values)
What is Data Mining SQL? Data Mining SQL Tutorial Guide for Beginner
SQL Data Types for Oracle PL/SQL, MySQL, SQL Server, and MS Access