Quick Reach
The delete statement
The delete statement of SQL is used to remove data from tables of the database.
The delete SQL statement removes existing data from the tables.
With a single delete query in SQL, you can remove whole data, a single row or multiple rows from the tables.
To remove single or specific rows from the table, the SQL Delete statement is used with the Where clause.
Where clause is used to filter and then delete rows from the table.
Practically, the delete command is used with the Where clause, it is generally least required to remove whole data from tables.
An example of delete statement
This tutorial will show you how to use delete query in SQL to remove whole data, a single row and multiple rows from a single table.
Syntax of Delete query
The general syntax of delete query is:
Delete from table_name
Where col1=val1 OR/AND col2=val2
In the above syntax, delete from is followed by the table name from where you want to remove the data. The where clause in delete SQL contains a condition that specifies which rows to be removed.
You can simply use:
Delete from table_name
OR
Delete * from table_name
However, this command will remove whole data from the table specified in delete query.
In order to use the delete from SQL statement to remove specific data, you have to use the Where clause. For example when you choose to delete an online email address of yours or some other account permanently. As such, email_accounts table may contain all email addresses of the account holders.
For the above example this is how delete query may look like:
1
2
3
|
Delete from tbl_email_accoutns
Where email_address=’your_address@email.com
|
As your email should be unique in a table so it will only delete the single row.
Examples of using delete statement
Let us show you examples of how to use delete with our created database and tables. We will use same database, test_db and tbl_employee table to show a few examples of delete statement.
Just to remind this is how our example table is created in test_db:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
Create table tbl_employee
(emp_id int,
emp_name varchar(100),
emp_age int,
emp_salary decimal(8,2),
joining_date datetime,
PRIMARY KEY (emp_id)
);
|
Now, as we have a table tbl_employee with five columns (emp_id, emp_name, emp_age, emp_salary and joining_date). Also, we have data by using the insert statement. For delete command examples, we have five rows of data in our table.
See Demo Table Online with five rows
Example of delete for single row
Consider that we want to remove a row of employee ‘Mike’ from our tbl_employee table that keeps only current employees data. This is how the Delete query would look to remove a record:
Demo Table before and after delete
1
2
3
|
Delete from tbl_employee
Where emp_name=‘Mike’
|
Example of removing data with two conditions in delete statement
Consider we have two employees with the same name ‘Mike’. Running the above delete query will remove both records in our tbl_employee table. In order to delete the record of employee ‘Mike’ who joined 2009-01-14, this is how we can remove it by using delete command of SQL.
This is how delete query in SQL would look:
1
2
3
|
Delete from tbl_employee
Where emp_name=‘Mike’ And joining_date = ‘ 2009-01-14’
|
Demo Table before and after SQL delete
In the above delete query, we used the And operator to add another condition inside the Where clause to differentiate between similar records.
The Delete example of removing whole data
As mentioned earlier, if you use delete statement without the where clause, it will delete whole data from the specified table.
Note that, it will only remove data, whereas physical table structure, constraints still exists.
To remove whole data from table, the delete statement for tbl_employee will be:
1
|
Delete from tbl_employee
|
Demo Table before and after delete
Also see SQL TRUNCATE TABLE
Leave A Comment?