SQL Delete Row Explained [10 Practical Examples]

Overview of SQL Delete Row

A delete request is expressed in much the same way as a query. We can delete only whole tuples that is row, we cannot delete values on only particular attributes.

SQL Delete Row is used to remove existing records from a table. We can delete a single record or multiple records depending on the condition we specify in the WHERE clause. If you omit the WHERE clause from a DELETE statement, SQL removes all the rows from the table

 

SQL Delete Row Syntax

Delete FROM table_source [ ,...n ] | view_name | table_alias  | <object>     
[ WHERE { <search_condition> | predicates } ]
[ORDER BY …]
[LIMIT row_count]

Here,

• FROM: An optional keyword that can be used between the DELETE keyword and the target table_or_view_name, or rowset_function_limited.
• table_alias: The alias specified in the FROM table_source clause representing the table or view from which the rows are to be deleted.
• table_source or_view_name: The name of the table or view from which the rows are to be removed.
• WHERE: Specifies the conditions used to limit the number of rows that are deleted. If a WHERE clause is not supplied, DELETE removes all the rows from the table.
• ORDER BY: If you specify the ORDER BY clause, the rows are deleted in specified order.
• LIMIT: The LIMIT clause is used to place a limit on the number of rows that can be deleted. These clauses apply to single-table deletes, but not multi-table deletes

SQL Delete first finds all tuples in table_source for which predicates is true and then deletes them from the table source

 

SQL Delete Row Examples

Consider Hospital database with five tables for performing SQL Delete examples
Patient Table

Doctor Table

Bill Table

Laboratory Table

Room Table

 

Example-1: SQL Delete All rows

Omitting Where clause with SQL delete row query will allow removing all rows from table source

In this example we will delete all rooms details from room table using SQL Delete row query

DELETE FROM room

• when we execute the above query it shows messages like 6 rows deleted or 6 rows affected because there are 6 records in the table source room
• the table room in not having any relation with any other tables of the same database so the system will allow for deletion, if it is not so the error message like ‘execution error the delete statement conflicted with the reference constraint foreign key’ in SQL server

OUTPUT:

To see the output of the SQL delete row, we need to use SQL select statement, the output shows an empty table structure as all rows has been deleted using the above query

SELECT room_no, room_type, status
FROM room

 

Example-2: SQL Delete row with WHERE condition

To delete multiple rows or set of rows SQL where clause is applied on SQL Delete row query to specified the condition

Use SQL Delete multiple Rows to remove all patient data who have paid room charges more than 800 from the bill table

DELETE FROM bill
WHERE (room_charge > 800)

• In the above query SQL where clause is used to specify where condition to delete patient data who have paid room charges more than 800
• When we execute the above query it shows a result message like 2 rows affected or deleted because there are two records that satisfied this condition

OUTPUT:

After executing the above SQL Delete row query the highlighted two rows will be removed from the table bill

 

Example-3: DELETE Statement with more than One Condition

With SQL Delete row, to delete rows based on two or more conditions the SQL where clause with relational operator is used with SQL Delete row query

Remove records from table bill if patient admitted for more than 5 days and paid room charges more than 900

DELETE FROM bill
WHERE (no_of_days > 5) AND (room_charge > 900)

• In above SQL Delete row query, two conditions has specified with where clause connected with relation operator AND, so if both conditions is stratified for a particular record than that row will be removed after executing above query
• When executing the above query, it shows the result as no row is affected because there are no records in the table bill which satisfied the above conditions

OUTPUT:

Select * from bill

 

Example-4: SQL Delete row using TOP with WHERE clause

SQL DELETE row with Top statement is used to delete the records from a table and limit the number of records deleted regarding a fixed value or percentage.

 

SQL DELETE Row with Top clause Syntax

Delete Top(top_value)[percent]
From table source
[where condition];

In this example we will delete maximum 5 records from table laboratory where bill amount is more than 800

DELETE TOP (5)
FROM laboratory
WHERE (amount > 800)

• When the above SQL delete row query is executed it will remove two records from the table of lab_no 50 and lab_no 60 which has amount value 900
• If more than 5 records exist which satisfied the condition of amount value more than 800 then only the top 5 records will be removed, others will not be deleted from the table

OUTPUT:

 

Example-5: SQL Delete row with ORDER BY clause

ORDER BY and LIMIT keyword can be used with SQL Delete Row to remove only a given number of rows, where columns are sorted in a specific order. The ORDER BY clause sorts the columns in a specific order and the LIMIT keyword deletes only the number rows mentioned by the numeric value immediately followed by the LIMIT keyword.

Delete highest two paid bill amount records from table bill using SQL Delete row

DELETE FROM bill 
ORDER BY billamount DESC LIMIT  2;

• When the above SQL Delete row query is executed first it orders the rows of the bill table in descending order according to column 'billamount',
• Then delete only two(2) rows from top

 

Example-6: SQL Delete Row from SELECT sub-query

SQL Delete row query can also remove the records based on the output of subquery used as the conditional parameter in where the condition

In this example we will remove the record from bill table which has minimum bill amount value as compare to all other bill amounts of patient

DELETE FROM bill
WHERE  (billamount =
(SELECT MIN(billamount) AS Expr1 FROM bill AS bill_1))

• When we execute the above query, the sub-query of the SQL select statement will execute first and the resulting value of the query which is the minimum amount value of bill amount is used as the condition value for the outer SQL Delete row query
• The above query will remove one row which is having a minimum amount value

OUTPUT:

Select * from bill

 

Example-7: SQL Delete row Using EXISTS with where clause

• ·You may wish to delete records in one table based on values in another table. The Exist clause is used with where clause to connect with sub-query
• The SQL EXISTS condition is used in combination with a subquery and is considered to be met, if the subquery returns at least one row

Use SQL Delete row query to remove vini doctor’s records from laboratory table

DELETE FROM laboratory
WHERE EXISTS
(SELECT  doctor_id, name, age, gender, address FROM doctor
WHERE (doctor_id = laboratory.doctor_id) AND (name = 'vini'))

• In the above SQL Delete row query, the inner sub-query executes first and return records where doctor name is vini and the records set which has value in referential table laboratory
• Then if the same record is existing in the laboratory table that record will be removed with SQL Delete row query

 

Example-8: SQL Delete Row to delete duplicate rows

SQL delete duplicate Rows using GROUP BY and HAVING clause

In this method, we use the SQL GROUP BY clause to identify the duplicate rows. The Group By clause groups data as per the defined columns and we can use the COUNT function to check the occurrence of a row.

Use SQL Delete Row query to remove duplicate rows from the laboratory table

To find duplicate records

SELECT patient_id, doctor_id, date, amount, COUNT(*) AS CNT
FROM laboratory
GROUP BY patient_id, doctor_id, date, amount
HAVING (COUNT(*) > 1)

• Above SQL select statement query will find and list duplicate records from laboratory table
• To remove these duplicate records, we can place this query as the sub-query in where clause of SQL Delete row
• We require to keep a single row and remove the duplicate rows. We need to remove only duplicate rows from the table

 

SQL Delete Row query to delete duplicate records

DELETE FROM laboratory
WHERE (lab_no NOT IN
(SELECT MAX(lab_no) AS Maxlabno FROM laboratory AS laboratory_1
GROUP BY patient_id, doctor_id, date, amount))

• When the above query is executed the one duplicate record from laboratory table will be deleted
• In the above query, we use the SQL MAX function to calculate the max id of each data row.

OUTPUT:

 

Example-9: SQL Delete Row with Row Number

In SQL ROW_NUMBER is a function that adds a unique incrementing number to the resulting recordset. The order, in which the row numbers are applied, is determined by the ORDER BY expression,

ROW_NUMBER function can be used with SQL Delete Row to delete Rows based on row numbers

SELECT bill_no, patient_id, doctor_charge,Row_number ()
 OVER (PARTITION BY bill_no ORDER BY bill_no) AS ROW_NBR 
 FROM bill

OUTPUT:

To delete first four rows based on order of bill number based using SQL Delete Row

DELETE FROM bill where (bill_no,doctor_charge) IN 
(select bill_no,doctor_charge from 
(SELECT bill_no, patient_id, doctor_charge,Row_number () OVER (PARTITION BY bill_no ORDER BY bill_no) AS ROW_NBR  FROM bill ) 
where ROW_NBR < 4 )

• In the above SQL Delete row query, the ROW_Number function is used in the innermost nested select query to generate row numbers based on an order by bill_no
• Then outer select statement will fetch the top 4-row number data from the resulting set, outermost SQL Delete row query will remove those 4 rows from the bill table

 

Example-10: SQL Delete row with foreign key

When any row is deleted in one table the same gets deleted in the foreign referenced tables that are referencing the primary key in that table, ON DELETE CASCADE is added while creating a table with create table statement

Example 10: create a table with a foreign key having on delete cascade functionality to delete records from the primary and referencing table both

CREATE TABLE newbill
(
  bill_no INT PRIMARY KEY,
  patient_id int,
  doctor_charge INT,
  room_charge int,
  no_of_days int,
  FOREIGN KEY(patient_id) 
  REFERENCES patient(patient_id)
   ON DELETE CASCADE
);

• After executing above crate table statement when the records deleted from primary table, the records present in foreign key table will automatically removed
• So, if the records of any patient are removed or delete from patient table, the same record will be deleted from foreign key table

 

Summary

In this article on SQL Delete row we have covered the complete explanation of SQL Delete row query with practical examples, first the use of Delete row command has explained with Syntax, then various examples like Delete All rows, multiple Rows, deletion based on where condition, SQL Delete row with exists clause and deletion of duplicate rows from a table with examples

 

References

SQL GROUB BY Statement
SQL WITH Clause

 

Further Reading

SQL DELETE Statement