Today's Offer - SQL Server Certification Training - Enroll at Flat 20% Off.

- SQL Server Blogs -

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. SQL Server Curriculum 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.

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.

Read: What is SQL Server? Microsoft SQL Server Tutorial Guide for Beginners

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? SQL Server quiz 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 –

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 –

Read: How To Become Expert In Sql Server Developer?

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 –


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: Introduction to SQL Injection and Attacks

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. free SQL Server demo 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.


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.

Read: Top 50 SAS Interview Questions and Answers For Fresher, Experienced

SQL Tutorial Overview

    Janbask Training

    A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.

Trending Courses


  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

6 days 02 Feb 2020


  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

0 day 27 Jan 2020

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

7 days 03 Feb 2020


  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

8 days 04 Feb 2020


  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

1 day 28 Jan 2020


  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

0 day 27 Jan 2020

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

0 day 27 Jan 2020

SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

4 days 31 Jan 2020


Search Posts


Receive Latest Materials and Offers on SQL Server Course