Deleting Data From A Table Using An INNER JOIN DELETE Query In MySQL 5.6.37

Earlier this week, I took a look at using an INNER JOIN within an UPDATE statement in MySQL in order to copy data from one table to another. To complement that post, I wanted to take a quick look at how an INNER JOIN can also be used in a DELETE statement to allow for the deleting of records that are constrained by a cross-table relationship in MySQL 5.6.37.

To recap the context of my previous post, I was retrofitting Markdown onto 15-years of HTML content using Lucee CFML. To do this, I was creating an intermediary MySQL table that contained programmatically-generated Markdown. And then, I was using an UPDATE with an INNER JOIN to copy the generated Markdown from the intermediary table over into the main entry table.

Given the two tables:

• blog_entry
• blog_entry_markdown_cleanup

... my MySQL UPDATE with INNER JOIN SQL statement looked like this:

-- The "cleanup" table contains our retrofitted markdown content produced from our data
-- sanitization task. We want to copy / move that markdown from the cleanup table into
-- the main entry table.
UPDATE
	blog_entry_markdown_cleanup c
INNER JOIN
	blog_entry e
ON
	(
			e.id = c.id
		AND
			-- NOTE: We've moved the filtering from the WHERE clause up into the ON
			-- clause. This is the PREFERRED APPROACH because it keeps all of the
			-- filtering logic COLLOCATED in the same portion of the query (making it
			-- easier to read - in my opinion).
			e.content_markdown = ''
	)
SET
	e.content_markdown = c.markdown,
	e.updatedAt = UTC_TIMESTAMP()
;

As you can see, this is copying the c.markdown column in the intermediary table over to the e.content_markdown column in the primary table.

Now, for the sake of discussion, let's assume that this kind of data migration task has to be run iteratively; and, we end up in a situation where only some of the rows have been processed. For example, imagine that we have this intermediary table as our Markdown cleanup:

mysql> SELECT * FROM blog_entry_markdown_cleanup;
+----+----------+
| id | markdown |
+----+----------+
|  1 | a        |
|  2 | b        |
|  3 | c        |
|  4 | d        |
+----+----------+
4 rows in set (0.00 sec)

And, we have this as our main blog_entry table:

mysql> SELECT * FROM blog_entry;
+----+----------+------------------+
| id | content  | content_markdown |
+----+----------+------------------+
|  1 | <p>a</p> |                  |
|  2 | <p>b</p> |                  |
|  3 | <p>c</p> | c                |
|  4 | <p>d</o> | d                |
+----+----------+------------------+
4 rows in set (0.00 sec)

As you can see, the Markdown content from the intermediary table has been partially copied over to the blog_entry table for two rows (3 and 4), leaving another two rows unprocessed (1 and 2). In such a scenario, we may want to delete the processed rows from the intermediary table such that we have a better sense of what data is left to be consumed.

To do this, we can run a MySQL DELETE query on the intermediary table that is constrained by an INNER JOIN on the main blog_entry table:

-- Let's delete the rows in the CLEANUP table that have already been copied over to the
-- main ENTRY table. To do this, we'll INNER JOIN on the id column and delete the rows in
-- the cleanup table that fulfill the INNER JOIN.
DELETE
	-- NOTE: This table list here can be a comma-delimited list of tables.
	c
FROM
	blog_entry_markdown_cleanup c
INNER JOIN
	blog_entry e
ON
	(
			e.id = c.id
		AND
			-- We only want to match on rows that show non-empty Markdown content in the
			-- main blog_entry table.
			LENGTH( e.content_markdown )
	)
;

As you can see, our MySQL DELETE statement is using an INNER JOIN to create a cross-product between the two tables based on the id column and the state of the content_markdown column. It then deletes any matching rows from the blog_entry_markdown_cleanup table (aliased as c).

NOTE: In this particular demo, I'm only delete from one of the tables; however, the MySQL DELETE statement can include multiple targets in this multi-table syntax. You simply need to include all of the targeted tables as a comma-delimited list:

DELETE a, b, c FROM a INNER JOIN b .....

... would delete the matching rows from tables a, b, and c.

If we execute the MySQL DELETE statement above, we end up with the following intermediary table:

mysql> SELECT * FROM blog_entry_markdown_cleanup;
+----+----------+
| id | markdown |
+----+----------+
|  1 | a        |
|  2 | b        |
+----+----------+
2 rows in set (0.00 sec)

As you can see, the two rows of Markdown that had been copied over to the blog_entry using the UPDATE / INNER JOIN have now been removed from the blog_entry_markdown_cleanup table using the DELETE / INNER JOIN.

Being able to use INNER JOIN (as well as other JOIN constructs) in MySQL UPDATE and DELETE queries has been tremendously helpful in my ColdFusion programming. This is especially true when we need to delete related data that has been spread across many, normalized tables.

Want to use code from this post? Check out the license.

Short link: https://bennadel.com/3796