Using A SQL JOIN In A SQL DELETE Statement (Thanks Pinal Dave!)

code-1.cfm

<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"
	/>