Earlier today, I posted about how John Eric dropped a bomb shell on me, demonstrating that you could run a SQL JOIN statement inside of a SQL UPDATE statement. Well, after reading my blog post, Pinal Dave of The SQL Authority followed up with another revelation - you can do the same thing inside of a SQL DELETE statement. The syntax for this is a bit strange, but perhaps equally powerful.
Taking my previous example with the @boy, @girl, and @relationship tables, I am now updating it such that we are going to delete all boys who have not had the skills to date Winona Ryder. Now, I am not saying that I would necessarily perform the SQL DELETE using this exact methodology, but certainly, it is fun to explore this SQL flexibility.
<cfquery name="qUpdateTest" datasource="#REQUEST.DSN.Source#"> <!--- Declare in-memory data tables. ---> DECLARE @boy TABLE ( id INT, name VARCHAR( 30 ), is_stud TINYINT ) ; DECLARE @girl TABLE ( id INT, name VARCHAR( 30 ) ) ; DECLARE @relationship TABLE ( boy_id INT, girl_id INT, date_started DATETIME, date_ended DATETIME ) ; <!--- Populate the boy table with some information. Notice that as I populate the IS_STUD column, all the values are going to be ZERO (meaning that these dudes are not very studly). This will be updated based on the relationship JOIN. ---> INSERT INTO @boy ( id, name, is_stud )( SELECT 1, 'Ben', 0 UNION ALL SELECT 2, 'Arnold', 0 UNION ALL SELECT 3, 'Vincent', 0 ); <!--- Populate the girl table with some information. ---> INSERT INTO @girl ( id, name )( SELECT 1, 'Maria Bello' UNION ALL SELECT 2, 'Christina Cox' UNION ALL SELECT 3, 'Winona Ryder' ); <!--- Populate the relationship table. ---> INSERT INTO @relationship ( boy_id, girl_id, date_started, date_ended )( SELECT 1, 1, '2007/01/01', NULL UNION ALL SELECT 1, 3, '2004/09/15', '2005/06/15' UNION ALL SELECT 2, 1, '2006/05/14', '2006/05/23' ); <!--- DELETE from the in-memory table. Here, we are going to join the boy, girl, and relationship table to see if any of the boys have NOT been studly enough to date Winona Ryder. We are only interested in keeping boys who have been in this sort of elite relationship. NOTE: Maria Bello would quite clearly be a studlier conquest, but I am trying to keep in line with my previous UPDATE demo. ---> DELETE b FROM @boy b LEFT OUTER JOIN ( @relationship r INNER JOIN @girl g ON ( r.girl_id = g.id AND g.name = 'Winona Ryder' ) ) ON b.id = r.boy_id WHERE g.id IS NULL ; <!--- To see if the delete has taken place, let's grab the records from the boy table; we should now ONLY have boys who have dated Winona Ryder. ---> SELECT id, name, is_stud FROM @boy ; </cfquery> <!--- Dump out the updated record set. ---> <cfdump var="#qUpdateTest#" label="Delete-Updated BOY Table" />
This code has a few cool things in it (in my opinion). For starters, we are demonstrating the whole point of this blog post - running the JOIN inside of the DELETE statement. Here we are using both an INNER JOIN and a LEFT OUTER JOIN. But, we are also performing a LEFT OUTER JOIN to the result of an INNER JOIN of two different tables; that in and of itself is a pretty nifty SQL ability. But anyway, take a look at the final syntax there - we are deleting a table from a table; a little strange, but I guess this is one of those things that you just need to get comfortable with.
Anyway, running the above code, we get the following CFDump output:
Notice that now the only record left in the @boy table is Ben - the only one who had a relationship with Winona Ryder. Pretty nifty stuff.
Thanks Pinal Dave!
Want to use code from this post? Check out the license.