Using A SQL JOIN In A SQL UPDATE Statement (Thanks John Eric!)

SQL UPDATE Used In Conjunction With SQL JOIN Statement

By Ben Nadel on September 4, 2007

I love learning new, cool stuff about SQL. It doesn't happen all that often (most of my SQL is fairly simple), but every now and then someone shows me something that just rocks my world, whether it be the power of Indexing or just something as simple as using UNION ALL instead of UNION. Last week, John Eric dropped a bomb shell on me, demonstrating how to update a table in conjunction with a SQL JOIN statement.

I have known for a long time that you could update a SQL View in Microsoft SQL Server (back when I used to use Views), so it makes sense that you could update a JOIN, but it never occurred to me to try this. Not only did it not occur to me, but the syntax used to do this is very strange to me (although now that I have stared at it for a long time, it's starting to make more sense).

Anyway, enough talk, let's take a look at this in action. Since I don't have any tables ready to play with, I have created three in-memory SQL tables: boy, girl, and relationship. The boy table lists boys, the girl table lists girls, and the relationship table lists out romantic relationships between the two (what can I say, I am a romantic fool at heart). Then, what I am going to do is UPDATE the boy table based on certain relationship criteria - in this case, anyone who has dated Winona Ryder is clearly a stud and should be flagged as such.

  • <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' );
  •  
  •   <!--- Update the in-memory table. Here, we are going to join the boy, girl, and relationship table to see if any of the boys have been studly enough to date Winona Ryder. If so, that BOY record will be updated date with the is_studly flag. ---> UPDATE b SET b.is_stud = 1 FROM @boy b INNER JOIN @relationship r ON b.id = r.boy_id INNER JOIN @girl g ON ( r.girl_id = g.id AND g.name = 'Winona Ryder' ) ;
  •  
  •   <!--- To see if the update has taken place, let's grab the records from the boy table. ---> SELECT id, name, is_stud FROM @boy ;
  • </cfquery>
  •  
  •  
  • <!--- Dump out the updated record set. --->
  • <cfdump var="#qUpdateTest#" label="Updated BOY Table" />
  • Notice how the SQL UPDATE statement is JOINing the @boy, @girl, and @relationship table using INNER JOINs and limiting it to boys who have dated Winona Ryder. The update is made to the result of that JOIN and then we are selecting all the rows from that updated @boy table (to see that it works). Running the above code, we get the following CFDump output:

    Source: www.bennadel.com

    Category: Forex

    Similar articles: