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:
... my MySQL
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
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
-- 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
NOTE: In this particular demo, I'm only delete from one of the tables; however, the MySQL
DELETEstatement 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
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
INNER JOIN have now been removed from the
blog_entry_markdown_cleanup table using the
Being able to use
INNER JOIN (as well as other
JOIN constructs) in MySQL
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.