The SQL DELETE statement deletes a row from a database table. You can use the WHERE statement to specify which records should be deleted. If you do not specify a where statement, every row in the table will be deleted.
Deleting rows is a common operation in SQL. For example, a user may decide to delete their account on your website. This will require you to remove their data from your user database.
You can use the SQL DELETE statement to remove any rows from your table. In this tutorial, we are going to discuss how you can perform an SQL delete row command on your database.
SQL Records Refresher
Databases are made up of a series of tables, each of which stores their own records.
For example, our company database may include tables on employees, suppliers, departments, payroll, and more. Each of those tables would include their own records, which are individual entries in a table. A payroll table may include a list of payments made to employees.
In order to add a new record to a table, we use an SQL INSERT statement. This allows us to add a record containing specific values to a column of our choosing.
Here’s an example of an SQL INSERT command in action:
INSERT INTO company_departments (name, department_id) VALUES ('Payroll', 5);
But what if we want to remove a row from our table? We can use the DELETE row statement to accomplish this goal.
SQL Delete Row from Table
The SQL DELETE statement removes a row or multiple rows from a table. The statement takes in two parameters. First, you should specify table name that the DELETE statement should focus on. Optionally, specify any conditions that must be met before a record is deleted.
Here is the syntax for a DELETE statement:
DELETE FROM table_name WHERE conditions_are_met;
A delete statement does not have any “undo” command. Once a record has been removed from a database, it cannot be recovered.
A WHERE statement lets you specify which records should be deleted. This uses the same syntax as an SQL WHERE statement with any other command, like SQL INSERT or UPDATE.
The DELETE statement does not delete a table. It will delete the records within that table.
Let’s take a look at an example of the SQL DELETE statement.
Delete Row SQL Example
If we want to delete only records that match a certain set of conditions, we can do so by specifying a WHERE clause. Let’s say that we have decided that the Marketing department should be merged into sales.
Our table currently contains the following records:
name | title | department_id |
John | Marketing | 2 |
Rebecca | Sales | 3 |
We’ll use a DELETE query to delete the Marketing department from our list:
DELETE FROM company_departments WHERE title = 'Marketing';
We can check if our record has been deleted using an SQL SELECT statement. Our result set is:
name | title | department_id |
Rebecca | Sales | 3 |
Rebecca’s employee record was not affected by our DELETE operation because we only deleted people whose title was equal to “Marketing”. But, John’s records is gone. This is because John worked for the marketing department.
In this case, we have deleted a single record. If there were more employees in the marketing department, their records would have been removed too.
Delete All Rows SQL Example
So, let’s say that we want to delete all the records in our company_departments table because we are undergoing a corporate reorganization. We could use this command to delete those records:
DELETE FROM company_departments;
This query removes all rows in our table company_departments. This is because we have not specified any conditions that must be met before a record is deleted. If you omit the where clause from any DELETE command, it will delete all rows within the table.
If we query our company_departments table to look for any employee with the title Marketing, we can see that there are no records:
name | title | department_id |
(0 rows)
Conclusion
The SQL DELETE statement removes rows from a database. This statement removes all rows from a database unless you specify a WHERE statement. The WHERE statement lets you control which records are removed.
In this tutorial, we discussed the basics of how rows are created in SQL, and. We then discussed how you can use the DELETE statement to remove a row from an SQL table. We have also discussed how you can delete records based on a certain condition, and delete all data in a table.
Are you looking to learn more about SQL? Check out our complete How to Learn SQL guide for a list of top learning resources and online courses.
"Career Karma entered my life when I needed it most and quickly helped me match with a bootcamp. Two months after graduating, I found my dream job that aligned with my values and goals in life!"
Venus, Software Engineer at Rockbot
About us: Career Karma is a platform designed to help job seekers find, research, and connect with job training programs to advance their careers. Learn about the CK publication.