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 »

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

Post Comment  |  Ask Ben  |  Permalink  |  Other Searches  |  Print Page

• Add To Del.icio.us
• Add To Digg
• Add to Spurl.net
• Add to Wists
• Add to Simpy
• Add to Newsvine
• Add to BlinkList
• Add to Furl
• Add to reddit
• Add to FARK
• Add to BlogMarks
• Add to Yahoo! My Web
• Add to smarking
• Add to Ma.gnolia
• Add to Segnalo

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

@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!

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.

@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.

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.

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

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". :)

@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.

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

@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.

Thanks for your help

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

http://www.rajib-bahar.com/rajib/BlogEngine.Web/post/2009/03/26/useful-blog-entries-on-Joins-using-join-in-update-or-delete-statement.aspx

I put a linkback to this entry because some of my colleagues/students may find it useful. All the best.

Effin ay, mang! You should teach a class! As far as blogs go, this one didn't suck. And I learned something. kudos.

@James,

Thanks my man. Glad you don't think my blog sucks :)

Excellent help!!

I am a fan of creating an array via XML, parsing it into a table, then using the above set-based insert/update/delete method.

By using XML and the above method, you can drop the execution time of multiple inserts in one transaction drastically. For example, if you had to loop over an insert statement; try changing it to creating XML with the same loop, then having SQL parse the XML into a temporary table and doing your insert via a select statement on the temporary table.

It is also possible to create a "CRUD" like stored procedure by adding a variable in the nodes to flag whether it is an insert, update or delete.

If anyone is interested in the XML CRUD stored procedure, let me know and I will see if I can dig it out of one of my past projects.

@eXcalibur.lk,

I am sorry, I don't follow what you are saying; but, I am intrigued. What XML are you talking about? And how are you turning that XML into an INSERT / UPDATE statement?

I am on holidays in another state at the moment, so I can not get my previous work; however here is an approach that is a little bit less verbose than my example: http://pratchev.blogspot.com/2008/11/import-xml-file-to-sql-table.html

When I get back, I will post my example. I did a proof of concept at my last job by looping over a cfquery with a sql insert and compared that to looping over an xml document, then doing the insert via sql parsing and table to table inserts. The results even surprised me.

@Andrew,

Oh wow, that's pretty cool. I've never seen that before. Do you know what databases this is valid for?

@Ben,

The functionality is for MS SQL (I think form 2005 on), I have not been able to find anything similar for other DBMSs; mind you have not been looking to hard, as currently all my projects have been with MS SQL.

@Andrew,

Gotcha, makes sense - MS SQL has some pretty powerful stuff.

Post Comment  |  Ask Ben

Post A Comment

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Formatting: <strong>bold</strong> <em>italic<em>
	Remember my information
	Subscribe to entry *
	Send me a copy of this comment (as part of the comment thread)

 

* If you subscribe, any new posts to this thread will be sent to your email address.