This tutorial will explain how to delete data from a table in SQL and how to use them with the examples.
This is the general statement to delete rows from a table
DELETE [ TOP ( number ) [ PERCENT ] ]
FROM table_name
[WHERE condition];
Lets create a sample table and add some rows
CREATE TABLE SampleContacts (
FullName VARCHAR(50) NOT NULL,
Gender CHAR(1) NOT NULL,
Age INT NOT NULL
);
INSERT INTO SampleContacts
(FullName, Gender, Age)
VALUES
('Contact1', 'M', 26),
('Contact2', 'M', 27),
('Contact3', 'F', 28),
('Contact4', 'F', 29),
('Contact5', 'M', 29),
('Contact6', 'M', 21),
('Contact7', 'F', 31),
('Contact8', 'F', 33),
('Contact9', 'F', 36),
('Contact10', 'M', 35),
('Contact11', 'F', 22),
('Contact12', 'M', 41),
('Contact13', 'M', 42),
1) Delete all rows from the table
The following DELETE statement will delete all the rows from SampleContacts table
DELETE FROM SampleContacts
2) Delete the number of rows example
The following DELETE statement will delete 10 rows from the SampleContacts table.
DELETE TOP (10) FROM SampleContacts
Above DELETE statement will delete first 10 records from the result. Where clause can be used to lets say 10 contacts who are younger than 35 to be deleted
DELETE TOP (10) FROM SampleContacts WHERE Age <35
3) Delete the percent of random rows example
The following DELETE statement will delete 3 percent of rows from the SampleContacts table:
DELETE TOP (3) PERCENT FROM SampleContacts
Above DELETE statement will delete first 3 percent records from the result. Where clause can be used to lets say 3 percent contacts who are older than 35 to be deleted
DELETE TOP (3) PERCENT FROM SampleContacts WHERE Age >35
4) Delete rows with a condition example
The following DELETE statement will delete records who are younger than 30 and gender equals to 'M'
DELETE FROM SampleContacts WHERE Age <30 AND Gender ='M'