Using A SQL JOIN In A SQL UPDATE Statement (Thanks John Eric!) : Kinky Solutions : A Student's Perspective by Ben Nadel

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

Posted September 4, 2007 at 10:31 AM

Tags: SQL

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.

Launch code in new window » Download code as text file »

<cfquery name="qUpdateTest" datasource="#REQUEST.DSN.Source#">
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
);
INSERT INTO @girl
(
id,
name
)(
SELECT 1, 'Maria Bello' UNION ALL
SELECT 2, 'Christina Cox' UNION ALL
SELECT 3, 'Winona Ryder'
);
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>
<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:

Notice that the Ben record was updated to reflect the Studly property. This is pretty cool stuff. Frankly, I haven't even ever used a FROM clause in my UPDATE statement (unless part of a sub-query). This is going to give me some cool stuff to explore.

Thanks John Eric!

Download Code Snippet ZIP File

Related Blog Entries

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

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

Kinky ColdFusion Calendar System Now Has An Application File

Keep your Web site content fresh and your overhead costs low with Savvy Content Manager

Reader Comments

Ben,

As general rule you can write all your UPDATE, DELETE query the same way you can write SELECT Query. They have same structure of syntax. You could also DELETE using JOIN as well. Try following query you will love it.

DELETE tableName
FROM tableName tn
INNER JOIN JoinedTable jn ON jn.col = tn.col
WHERE jn = someval

I see where you are going. Similar thing happened to me.

I was biggest fan of ColdFusion and can not stop thinking about it. One day, I started to learn SQL and here it goes. I could not come back to ColdFusion from SQL.

I guess, Once you go SQL...

Pinal

Posted by Pinal Dave on Sep 4, 2007 at 10:46 AM

@Pinal Dave,

Cool stuff. I didn't know you could use this in DELETE as well. Awesome stuff. One of the things I have always wanted to do is create a table alias in the UPDATE statement, but it has always failed due to Syntax error. That was before I knew you could do a FROM clause in the UPDATE statement. This should make things much easier, especially when dealing with an UPDATE that uses a sub-query.

Good stuff!

Posted by Ben Nadel on Sep 4, 2007 at 11:18 AM

Pinal --> For me it's been using them both together. I've seen some people write a lot of CF code to do something they could do in just a few SQL commands passed off via CFQuery.

Posted by Allen on Sep 4, 2007 at 2:47 PM

@Pinal,

Works like a charm!

http://www.bennadel.com/index.cfm?dax=blog:939.view

The syntax is a bit strange to me, but I just need to get used to it. Thanks for the hot tip.

Posted by Ben Nadel on Sep 4, 2007 at 2:48 PM

I am with Allen on this one. I don't think one is necessarily better than the other - they do different things. They work together to create harmony. The trick is to know when to leverage the powers of each tool.

Posted by Ben Nadel on Sep 4, 2007 at 2:50 PM

Ben,

I am sure my blog readers will find it useful.
http://blog.sqlauthority.com/2007/09/05/sqlauthority-news-interesting-read-using-a-sql-join-in-a-sql-updatedelete-statement-ben-nadel/

Regards,
Pinal

Posted by Pinal Dave on Sep 5, 2007 at 10:51 AM

How odd that I would stumble across this. Recently I started using UPDATE...SET...FROM to persist an array of objects in one SQL statement.

Essentially, I pass an array of components to a persistence object, and construct an in-memory table within the UPDATE statement, looping through the array of objects to be persisted and adding a UNION ALL after every loop iteration. Except the last one, of course.

Works well, and is much, much faster than using one SQL statement per loop iteration. Although, I'll admit upfront that my measurement process consists of running the app and saying "Well, that seemed a lot faster". :)

Posted by Matt Osbun on Mar 10, 2008 at 9:55 AM

@Matt,

If you think about applications from a "user experience" standpoint, it's the "that runs faster" moment much more important than any numeric reading? As long as you perceive it to be faster, that's all that counts.

Posted by Ben Nadel on Mar 10, 2008 at 10:00 AM

Ben and Pinal

Hi guys!

I was wondering if this technique could be modified to set is_stud to the value of one of the other tables in the join (say, g.id) instead of a static 1.

This would be very useful for me in a situation I've been finding myself in frequently.

I have a table of countries (country_id, country_name etc.) and a new table (people) with a country_name field that matches up with countries.country_name

I would like to set people.country_id (which for all records is currently set to zero since the data is new) equal to countries.country_id where the country names in each table match.

Is this achievable with just SQL and not CF as I have been doing?

I'm just not sure of values can be returned from the join conditions being met.

Thanks and keep up the good help :)

Steve

Posted by Steve Lionbird on May 12, 2008 at 12:16 PM

@Steve,

Yes, you can update one table using the value in another table in the manor. Create the JOIN that you think is appropriate and then set one column to the other.

Posted by Ben Nadel on May 13, 2008 at 8:05 AM

Thanks for your help

Posted by Guillaume on Jul 9, 2008 at 10:56 AM

I like any piece of code that contains the line INSERT INTO Girl ;-)

Posted by zaphekiah on Jul 30, 2008 at 7:50 AM

Post Comment | Ask Ben

Using A SQL JOIN In A SQL UPDATE Statement (Thanks John Eric!) : Kinky Solutions : A Student's Perspective by Ben Nadel