Top 5 MySQL DELETE Syntax for T-SQL Developers

Our journey to MySQL started with CREATE TABLE followed by INSERT. Today, we are proceeding to MySQL DELETE. Since you are familiar with T-SQL DELETE, the goal is to make you more productive using the MySQL syntax.

There are minor differences but there’s nothing that you can’t handle. Let’s get started.

CodingSight - Top 5 MySQL DELETE Syntax for T-SQL Developers

Preparing the Data

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.

But before importing data, we should come up with a database named tv-series with two tables called Titles and Episodes. In Figures 1 and 2 you can view the visual table structure displayed by dbForge Studio for MySQL.

TV series Titles table structure
Figure 1. TV series Titles table structure
TV series Episodes table structure.
Figure 2. TV series Episodes table structure.

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

1. Delete All Records Using MySQL DELETE

First, the easiest. Let’s try to back up the Episodes table and then delete the records of the backup.

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 all records, 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. Conditional Delete with WHERE Clause

The second point is about using DELETE with a condition. This way, you can delete 1 or more records in a single statement. But not all. 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. Using LIMIT in MySQL DELETE

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:

Limiting the result to 2 records or 2 episodes from 1987 Season 2 of MacGyver.
Figure 3. Limiting the result to 2 records or 2 episodes from 1987 Season 2 of MacGyver.

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 sort them into season and episode numbers. Finally, limit it to 2 records. We’ll delete that result.

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. Using JOIN in MySQL DELETE

Another way to delete records is using it with a JOIN that will filter records you want to delete. 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. MySQL DELETE with a Subquery

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 a 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

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.

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.

Edwin Sanchez

Edwin Sanchez

Software developer and project manager with a total of 20+ years of software development. His most recent technology preferences include C#, SQL Server BI Stack, Power BI, and Sharepoint. Edwin combines his technical knowledge with his most recent content writing skills to help new breed of technology enthusiasts.

Leave a Reply

Your email address will not be published. Required fields are marked *