TOP 5 MySQL Delete Syntax with Tips for T-SQL Developers

Our journey to MySQL started with CREATE TABLE followed by INSERT. Today, we are proceeding to the MySQL DELETE statement. It is almost as common as the MySQL UPDATE statement.

Since you are familiar with T-SQL DELETE, the goal is to make you more productive using the MySQL syntax. There are minor differences in these DELETE statements, but there’s nothing that you can’t handle. Let’s get started.

Preparing the Sample Data

One of the most common needs is the MySQL delete row task. We’ll examine it here, but first, we have to prepare our working area. To delete any records, we first need to have them. Thus, let’s build a database in MySQL.

I’m feeling nostalgic about TV series from the ’80s, so I’m going to use episodes from MacGyver and Quantum Leap for sample data. I have the final Excel file to import the data into a new database. This file has 2 sheets named Titles and Episodes. We will import them both into two tables.

Before importing data, we should come up with a database named tv-series with two tables called Titles and Episodes. The MySQL create table job is elementary with the proper tool. I am using dbForge Studio for MySQL, and the following Figures 1 and 2 display the visual table structure of my database presented by that tool.

The script for the tables is below:

CREATE DATABASE IF NOT EXISTS `tv-series`;

USE `tv-series`;

CREATE TABLE `tv-series`.titles (
  TitleID int NOT NULL AUTO_INCREMENT,
  Title varchar(50) DEFAULT NULL,
  `From` int DEFAULT NULL,
  `To` int DEFAULT NULL,
  PRIMARY KEY (TitleID)
)
ENGINE = INNODB,
AUTO_INCREMENT = 3,
AVG_ROW_LENGTH = 8192,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_0900_ai_ci;

CREATE TABLE `tv-series`.episodes (
  EpisodeID int NOT NULL AUTO_INCREMENT,
  TitleID int DEFAULT NULL,
  EpisodeTitle varchar(100) DEFAULT NULL,
  AirDate date DEFAULT NULL,
  Synopsis text DEFAULT NULL,
  EpisodeNo int NOT NULL,
  SeasonNo int NOT NULL,
  PRIMARY KEY (EpisodeID)
)
ENGINE = INNODB,
AUTO_INCREMENT = 160,
AVG_ROW_LENGTH = 414,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_0900_ai_ci;

ALTER TABLE `tv-series`.episodes
ADD INDEX IDX_episodes_TitleID (TitleID);

After creating the database and tables, we can import our data from the Excel file.

The most straightforward and powerful tool I used to import Excel files to MySQL is dbForge Studio for MySQL. I especially appreciate the addition of a new column as a primary key in the target table. Also, the autodetect date and time format is great support. It converts dates with a text data type and strange date formats without hassles.

Since we are going to compare the MySQL syntax to T-SQL, you may also import the Excel file to a new database on SQL Server. Then, use the same database and table names as in MySQL.

More Information

• MySQL import and export tool in dbForge Studio for MySQL

1. Delete All Data from Table

First, the easiest. Let’s try to back up the Episodes table and then delete the records of the backup. We’ll perform the standard MySQL delete all rows operation that you may need to do in different situations.

CREATE TABLE episodes_backup AS
SELECT * FROM episodes;

DELETE FROM episodes_backup;

As you see, the syntax is the same as for T-SQL. The query will delete all records in the episodes_backup table.

A few notes for you:

1. If you need to delete multiple rows or all rows, TRUNCATE TABLE is faster than DELETE without a WHERE clause. This is true for both MySQL and SQL Server.
2. Executing the above code in MySQL Workbench will trigger an Error Code 1175. It’s not allowed because MySQL Workbench defaults to safe mode update. Thus, a delete or update to a table without a WHERE clause is not allowed. Yet you can disable this feature in Preferences.

2. Delete WHERE in MySQL

The second point is about using DELETE with a condition, the MySQL delete from select operation. This way, you can delete one or more records in a single statement, but not all of them. For instance, delete first row or other specific row/rows. What if you are looking for a way of how to avoid duplicate records in SQL? Using the WHERE condition would be the solution.

Again, the syntax is the same in T-SQL:

-- Delete 1 record using an equality operator
DELETE FROM episodes
WHERE EpisodeID = 150;

-- Delete 3 records using IN
DELETE FROM episodes
WHERE EpisodeID IN (151,152,153);

Nothing extraordinary here, but the next section will be different.

3. MySQL DELETE LIMIT

LIMIT? What LIMIT?

LIMIT is equivalent to TOP in T-SQL. Before we have a DELETE example, let’s query records we are going to delete. Check out the code below:

SELECT * FROM episodes
WHERE year(AirDate) = 1987
ORDER BY SeasonNo, EpisodeNo
LIMIT 2;

Here is your LIMIT. It will result in 2 records as shown in Figure 3 for the card view in dbForge Studio for MySQL:

Now, let’s try to delete them. Take a look at the example below:

DELETE FROM episodes
WHERE year(AirDate) = 1987
ORDER BY SeasonNo, EpisodeNo
LIMIT 2;

Before deleting 2 episodes, MySQL queried all episodes from 1987. Then it sorted them into season and episode numbers. Finally, it limited the results to 2 records. It is also a suitable method when we need to delete duplicate rows. It lets us specify the conditions for deletion and make sure that we won’t delete anything necessary (but making a backup would still grant the data safety).

Is there a T-SQL equivalent? Do you think this will work?

DELETE TOP (2) FROM episodes
WHERE year(AirDate) = 2020
ORDER BY SeasonNo, EpisodeNo
GO

If you paste the above code in SQL Server Management Studio, you’ll get squiggly lines in the ORDER BY clause. Yet, TOP is allowed.

If you remove the ORDER BY clause, though, your query will delete 2 random records. Finally, the above code will not run as expected.

To have the same result as in MySQL, you need the following code:

DELETE FROM episodes
WHERE EpisodeID IN
(SELECT TOP 2 EpisodeID FROM Episodes
 WHERE YEAR(AirDate) = 1987
 ORDER BY SeasonNo, EpisodeNo)

It’s a bit longer in T-SQL than in MySQL. But there’s more.

4. MySQL DELETE with JOIN

Another way to delete records is using it with a JOIN that will filter records you want to delete. As many users define it, it is the MySQL delete join task. Nothing looks different when you’re not using a table alias. Check the example below:

DELETE episodes
FROM episodes
INNER JOIN titles ON titles.TitleID = episodes.TitleID
WHERE titles.TitleID = 3
AND episodes.SeasonNo = 3;

This will also work in T-SQL. But if we use a table alias like this:

DELETE episodes
FROM episodes e
INNER JOIN titles t ON t.TitleID = e.TitleID
WHERE t.TitleID = 3
AND e.SeasonNo = 3;

MySQL will throw an error. The correct syntax is below:

DELETE e
FROM episodes e
INNER JOIN titles t ON t.TitleID = e.TitleID
WHERE t.TitleID = 3
AND e.SeasonNo = 3;

Note the difference!

5. Delete Subquery in MySQL

Finally, we try using subqueries. It is a different way to filter records you want to delete. You already saw the example one earlier, but let’s have it again:

DELETE FROM episodes
WHERE EpisodeID IN
(SELECT TOP 2 EpisodeID FROM Episodes
 WHERE YEAR(AirDate) = 1987
 ORDER BY SeasonNo, EpisodeNo)

This is perfectly valid syntax in T-SQL. But will it work in MySQL? Let’s change it slightly:

DELETE FROM episodes
WHERE EpisodeID IN
(SELECT EpisodeID FROM Episodes
 WHERE YEAR(AirDate) = 1987
 ORDER BY SeasonNo, EpisodeNo
 LIMIT 2);

Oops! Looks like we hit the wall. Here’s what MySQL says:

This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’

What if we remove the LIMIT keyword? It triggers another error:

You can’t specify the target table ‘episodes’ for an update in FROM clause.

Is there a workaround? Changing the code to a nested subquery will run just fine:

DELETE FROM episodes
WHERE EpisodeID IN
(SELECT EpisodeID FROM (SELECT EpisodeID FROM Episodes
 WHERE YEAR(AirDate) = 1987
 ORDER BY SeasonNo, EpisodeNo
 LIMIT 5) AS T)

That should do it. But let’s have a more sensible example:

DELETE FROM episodes
WHERE TitleID IN
(SELECT TitleID FROM titles
 WHERE `From` BETWEEN 1995 AND 2000);

Now, what’s the big deal with these examples? Let’s see:

1. MySQL won’t accept the DELETE statement with a subquery filter using the LIMIT keyword. Not yet.
2. MySQL won’t accept DELETE from the target table equal to the subquery table filtering it.

Get your FREE PDF with useful code examples below

[sendpulse-form id=”13566″]

Conclusion

So far so good. You have three DELETE syntax comparisons for MySQL and T-SQL. I hope the third installment will also prove useful to you in the future.

This post highlighted the MySQL syntax when deleting

• All records,
• 1 or more records with a WHERE clause,
• Using LIMIT,
• Using JOIN,
• Using subqueries.

You can easily see the ways of the MySQL delete column task performance, or delete any row, multiple rows, and specific records by conditions.

If you liked this post, feel free to share it on your favorite social media platforms. Let us also know what you think in the Comments section below.

Tags: mysql, mysql delete, sql delete, t-sql Last modified: March 30, 2023