Using A SQL JOIN In A SQL UPDATE Statement (Thanks John Eric!)
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 ) ;
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 );
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