Deleting rows is a common operation in
SQL. For example, a user may decide to delete their account on your website, and so you will need to remove their data from your user database. Or you may stop selling a product and decide to remove it from your product’s database.
You can use the
SQL DELETE statement to remove any rows from your table, just like you would use the
INSERT command to add a new row to a table.
In this tutorial, we are going to break down how you can perform an
SQL delete row command on your database, and remove any records you no longer want to store.
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. For example, 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
INSERT statement. This allows us to add a record containing specific values to a column of our choosing. Here’s the syntax for an
INSERT INTO table_name (column1_name, column2_name) VALUES (column1_value, column2_value);
As you can see, our command takes in a few parameters. Firstly, our command needs to know the name of the table where we will add our record. Then, it needs a list of the column names where we will add data.
After we specify those names, we need to tell our
INSERT command which column names should have which values. These are listed in order of the column names, so the first column name we list will be given the first value that we assign in the
VALUES section of our statement.
Here’s an example of an
SQL INSERT command in action:
INSERT INTO company_departments (name, department_id) VALUES ('Payroll', 5);
When we query our database to find out our company departments, we can see that our payroll department has been added:
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
SQL DELETE statement can be used to remove one or more rows from a specific table. The statement takes in two parameters: the table name that the
DELETE statement should focus on, and any conditions you want to specify. Here is the syntax for a
DELETE FROM table_name WHERE conditions_are_met;
So, let’s say that we want to delete all the records in our
departments table because we are undergoing a corporate reorganization. We could use this command to delete those records:
DELETE FROM company_departments;
This query would remove all rows in our table
company_departments. Indeed, if you omit the where clause from any
DELETE command, it will delete all rows within the table.
In addition, if we want to delete only records that match a certain set of conditions, we can do so by specifying a
WHERE clause. So, if we have decided that the position of
Marketing Director should be made redundant, and the VP of marketing will take over the role, we could use this query to remove the employee who is currently the marketing director from our database:
DELETE FROM company_departments WHERE title = 'Marketing Director';
Now, if we query our
employees table to look for any employee with the title
Marketing Director, we can see that there are no records:
DELETE statement does not mean a table will be deleted, rather it will delete the records within that table.
In this tutorial, we have broken down the basics of how rows are created in
SQL, and we have 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.
Now you should be able to remove
SQL database rows like an expert!