3rd Nov 2021 7 minutes read What Is the DELETE Statement in SQL? Andrew Bone sql DELETE Table of Contents SQL DELETE Syntax SQL DELETE Permissions SQL DELETE Examples Simple DELETE Subquery DELETE Deleting Primary Key and Foreign Key Constraints Keep Your Data Relevant with SQL DELETE This article will explain the use of the DELETE statement, one of the primary methods of removing existing records from your database. First we will run through the syntax, followed by some easy-to-understand examples. The DELETE statement in SQL is extremely useful. It allows you to remove any obsolete or “bad” data from your database tables. You should exercise caution when using the DELETE statement, as the operation is not reversible. There will be no dialog box asking you to confirm the operation. Once you run a DELETE query, the data will be gone – forever! Question: What is SQL Delete? A SQL DELETE command removes existing rows from a database table. It can target one or more rows based on conditions specified in the WHERE clause. Without WHERE, it can delete all rows in a table - so use DELETE with caution. The DELETE statement is known as a data manipulation command. This article covers the other commands of this type, which are INSERT and UPDATE. Let’s explore the syntax of SQL DELETE. SQL DELETE Syntax The syntax of the DELETE statement is as follows: DELETE FROM [WHERE ] The table you would like to delete data from comes after the DELETE statement. The square brackets indicate that this WHERE clause is entirely optional. But in practice, you’ll use this clause almost all the time – if you do not include a WHERE clause, all of the data from the table will get deleted. That’s why it’s always a good idea to use SELECT to view the data you would like to delete. When the result from your SELECT query equals the data you intend to delete, simply replace the SELECT in your query with the DELETE statement. Every other part of your query can remain the same. This will ensure that you do not accidentally delete some vital data by mistake. That concludes our look at the SQL DELETE syntax. It’s time to look at some practical examples, which will demonstrate some different uses of the DELETE statement and touch on some permissions issues you may face. SQL DELETE Permissions Before reviewing our examples, it is important to note that (depending on what SQL dialect you use) you may have to change user permissions so you can modify records with the DELETE statement. By default, MySQL Workbench starts in safe mode; you cannot use UPDATE or DELETE without a WHERE condition. For example, when using MySQL you will encounter the message: “You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences → SQL Editor and reconnect”. To rectify this, you have two options. Change your settings like so: Go to Edit → Click the SQL Editor tab and uncheck the Safe Updates check box. Click Query → Reconnect to Server. Execute your SQL query. Or you can run the following query: SET SQL_SAFE_UPDATES = 0; After completing either of these options, you should be able to use DELETE to remove the data from your tables. Now, let’s move on to the examples! SQL DELETE Examples Simple DELETE Imagine we’re maintaining a database of employees and we’ve just got word that some of our employees have left the company. We’re going to remove them from our employees table. Let’s take a look at the current state of this table: employee_idfirst_namelast_nameage 1JohnLennon34 2PaulMcCartney36 3GeorgeHarrison41 4RingoStarr49 Imagine we had to remove George Harrison and Ringo Starr from our table. What are our options? We should make sure of the employee_id column values before we remove these records. After we did that, the DELETE query would look like this: DELETE FROM employees WHERE employee_id = 3 OR employee_id = 4 This would successfully remove George and Ringo from our table. You can also specify a single condition that would capture both of these records at once. In this case, as both George and Ringo are over 40 years old, we could use the age column: DELETE FROM employees WHERE age > 40 Executing this query will leave us with the following records in our employees table. employee_idfirst_namelast_nameage 1JohnLennon34 2PaulMcCartney36 Our records have been successfully deleted! If you want to see more DELETE examples, check out this cookbook article, which shows you how to delete a single or multiple rows of data. Let’s next consider a more advanced example that uses a subquery to delete data. Subquery DELETE You can also delete data from your tables using subqueries. A subquery is when one query is used as part of another. You can learn more about subqueries by reading this beginner-friendly article. Let’s look at a scenario where we can use a DELETE with a subquery. We’ll use our employees table again: employee_idfirst_namelast_nameage 1JohnLennon34 2PaulMcCartney36 3GeorgeHarrison41 4RingoStarr49 We also have a sales_team table that contains records for everyone in the company’s sales department: idfirst_namelast_name 1JohnLennon 3GeorgeHarrison We’ve just received the terrible news that the sales team is being let go. It’s our job to update the employees and sales_team tables. I’ll demonstrate how a subquery can help us in this particular scenario. Here’s the query: DELETE FROM employees WHERE employee_id IN (SELECT id FROM sales_team) Ok, let’s break this down. The query in round brackets (i.e. the subquery) returns all id values from the sales_team table: SELECT id FROM sales_team This query gives us the values 1 and 3. Let’s look at the DELETE part of the main query: DELETE FROM employees WHERE employee_id IN... Notice the WHERE clause. In this part of the query, we are looking to delete records where the employee_id is in the results of our subquery. Remember the subquery evaluated to 1 and 3. So, this query evaluates to: DELETE FROM employees WHERE employee_id IN (1,3) Suppose we execute the whole query and use the SELECT query below to view the results: SELECT employee_id, first_name, last_name, age FROM employees This is what we get: employee_idfirst_namelast_nameage 2PaulMcCartney36 4RingoStarr49 There we have it! The sales team was successfully removed from the employees table. You can see how useful a subquery was for this use case. This has been a slightly more advanced use case. When discussing the DELETE statement, it is worth mentioning the TRUNCATE and DROP statements: TRUNCATE is faster than DELETE, as it does not scan the table before removing records. Because of this fact, it cannot be used with the WHERE condition or tell you how many rows were deleted when the operation is complete. The DROP TABLE command will remove everything to do with that table: the table definition and all related indexes, triggers, and constraints. To learn more about these two methods of removing data, read this article about how to use subqueries with INSERT, UPDATE, and DELETE. You can also check out this this article that discusses the difference between DELETE, TRUNCATE, and DROP. Deleting Primary Key and Foreign Key Constraints One more thing we must discuss is the primary key (PK) and foreign key (FK) constraints. When working on smaller examples like the ones shown in this article, you will not have to worry about PK and FK constraints. But if you work with a large existing database, you will definitely encounter them. When deleting data from a table, you may also want to remove any constraints associated with the data in that table. Check out the following examples if this applies to you: This example shows you how to delete a primary key. This example shows you how to delete a foreign key constraint. Keep Your Data Relevant with SQL DELETE That concludes our comprehensive look at the SQL DELETE statement. We’ve demonstrated its syntax and explored potential uses of this command through several examples. If you’d like a more comprehensive SQL education, check out our A-Z SQL learning track that teaches you the basics and then moves onto more advanced topics. Tags: sql DELETE