SQL Delete Row Table Statement & Duplicate Rows 2024

What We Are Learn:

SQL Delete Row Table Statement: The SQL DELETE statement is a DML (data manipulation language) command that can be used to delete one or more rows of information from a database table, view, or user-defined function. This command does not completely delete the records; instead, it marks them for deletion and allows the transaction to be reversed in case needed.

We can use the DELETE statement with conditional and limiting SQL clauses such as TOP or WHERE to filter out specific rows. While it may seem more useful due to its filtering options, using the DELETE command for deleting all records from a table slows down performance drastically. Fortunately, we can utilize the TRUNCATE command much faster than any other method available.

It is essential to be completely certain before utilizing this command, as it will delete the data irreversibly.

SQL Delete Syntax

As stated earlier, the SQL Server delete statement removes one or multiple entries from a table or view. The syntax for this command is as follows:

DELETE FROM [Table_Name] 
WHERE Condition;

In this above syntax, Table_Name is the name of the table from which you want to delete records, and the condition is an expression that specifies which records to delete. If you omit the WHERE clause, all records in the table will be deleted.

SQL Delete A Row

We can specify filters or conditions in the “WHERE” clause to determine whether a SQL Server statement deletes records. If you do not include this clause, it could result in all rows being deleted from the table.

DELETE FROM EMPLOYEE
WHERE EMPNO=14;

After executing the above SQL Delete query, it will delete the record whose empno is 14.

SQL Delete Row – Delete Multiple records

In this multiple records delete example, we will use the AND operator’s help in the WHERE clause to evaluate multiple conditions before removing any record.

DELETE FROM EMPLOYEE
WHERE SAL=3000 AND DEPTID=4;

The above query will delete all the records with a salary of 3000 and a deptid of 4.

SQL Delete Statement – Delete Top Clause

In this SQL Server example, we use the Select TOP to delete the first record with an annual income of less than or equal to 2000. Here is the table looks before deleting the record

But when we execute the below script:

DELETE TOP(1)
FROM EMPLOYEE
WHERE SAL<=2000;

This query will check for records with a salary of 2000 or lower. Then, the TOP Clause will select the first record that fulfills these criteria and delete the record.

SQL Delete Row – Using Between Operator

If you want to remove multiple records, then by using the Between Operator, we can delete the records, and here is an example.

DELETE
FROM EMPLOYEE
WHERE SAL BETWEEN 1100 AND 2450;

When you execute the above query, it deletes all the records whose salary falls between 1100 and 2450.

In the between operator, if you place the higher value first and later the small value, you will not get any error, and no records will be deleted.

SQL Delete Row – IS NULL Example

In this example, we will see how to delete all employees with a NULL value. As we have seen in the MGR Column, a few records have the null value, and we will try to delete all those values.

Here is an example:

DELETE
FROM EMPLOYEE
WHERE MGR IS NULL;

After executing the above query, if you see the records of the employee’s table, there is no NULL value in the MGR column.

SQL Delete Statement – Using Management Studio

If you can access the Management Studio in SQL Server, use Intellisense to help compose this statement. To do so, right-click on the table and select “Script Table as,” then choose “DeleteTo,” which will open up a new query editor window for you.

You must write the delete query on that editor window to delete the records.

SQL Delete Table – All Records

In the above example, you notice we have used the Where clause to select the required records, but if you want to delete all the records or have forgotten to add the where clause in the SQL Delete Statement, that will delete all the records of that table.

DELETE FROM EMPLOYEE;

Conclusion:

Now that you have mastered deleting records, it is time to move on and learn more about other SQL commands. With this knowledge, you can begin manipulating and querying data which will take your journey toward becoming a SQL expert one step further!

We would love for you to ask us any questions about our “SQL Delete Row” article. Please comment directly on the page; we will do our best to respond with helpful solutions.

SQL Delete