ColdFusion Muse

Leveraging Your SQL - Update Using a Join

Most update queries are pretty straightforward. You already know the primary key or some other criteria for a single table and and the WHERE clause is just "WHERE pk_id = 4" or "WHERE area_code = 312". There are times, however, when it might be useful to update a table based on critria from 2 or more tables. For a purely hypothetical example, let's say I have a shipping amount in an "ordShip" table, a base amount in an "orders" table and I have a tax amount (as a decimal) in a tax table based on the state. Let's also assume I have the state in the "orders" table. I want to update the "grandTotal" amount in the "orders" table. The formula would be:

base Amount + (base Amount * tax rate) + shipping.
How would I go about it?

The Usual suspect

Here's what I see more often than not (forgive me if I do not use CFQUERYPARAM. I'm saving space on the page).

<!--- get the order --->
<cfquery name="getOrd" datasource="#dsn#">
   SELECT    baseAmt, State
   FROM   Orders
   WHERE   ord_id = #val(ord_id)#
<!--- get the tax rate --->
<cfquery name="getTax" datasource="#dsn#">
   SELECT    taxRate
   FROM   stateTaxRate
   WHERE   State = '#getOrd.state#'
<!--- get the shipping cost --->
<cfquery name="getShip" datasource="#dsn#">
   SELECT    shipAmt
   FROM   ordShip
   WHERE   ord_id = #val(ord_id)#
<!--- add them together --->
<Cfset gTotal = val(getOrd.baseAmt) + (val(getOrd.baseamt) * val(getTax.taxRate)) + val(getShip.shipAmt)>
<!--- update orders --->
<cfquery name="updateOrd" datasource="#dsn#">
   UPDATE orders
   SET      grandTotal = #gTotal#
   WHERE   ord_id = #val(ord_id)#
It's not pretty but it is effective. It gets the job done and you can see what is happening.

Leverage your SQL

What you may not know is that you can update 1 or more tables and use a JOIN in the process. Here's an example.

<cfquery name="getOrd" datasource="#dsn#">
   SET    O.grandTotal = O.baseAmt + (O.baseAmt * T.taxRate) + S.shipAmt
   FROM   orders O JOIN ordShip S
         ON O.ord_id = S.ord_id
   JOIN   stateTaxRate T ON O.state = T.state
   WHERE   O.ord_id = #val(ord_id)#
You could also do the same thing with sub-selects, but I like this syntax better. I think it's cleaner and easier to grasp. I hate digging through levels of sub-selects to figure out a query.

There is 1 gotcha. I have tried and failed to make this work without table aliases (to use the actual table qualifiers - orders.grandTotal instead of O.grandtotal). I'm not sure what the hang up is. There probably IS a way to do it (perhaps with quoted identifiers). I have just not sorted it out. Since I always use aliases in a JOIN query anyway it is not a limiting factor for me. I would also add that you should use extreme caution. If your JOIN is not a straight "1 to 1" you should make sure you know exactly what it is doing. Remember you are updating tables based on that join. You could have unexpected results.

Jason Nussbaum's Gravatar Assuming you're using SQL Server (which it looks like you are), you could always try using a View.

Cleaner on the SQL side, because you don't need to run the join in the UPDATE statement - you can simply update the view and use a standard where clause.

Another advantage is that you can actually update columns in more than one table (I'm not sure whether your script above is able to do that).
# Posted By Jason Nussbaum | 7/15/05 11:28 AM
Mkruger's Gravatar Yes - a view works as well, but an update in MS SQL can <i>only affect 1 of the tables in the view</i>. See the note titled "SQL Views" under "SQL Architecture" in the MS SQL "books online". That means you often have to go "check" the view to make sure you know the column source tables. I'm ok with it as long as the view is straightforward, but it could obfuscate what is really going on in some cases - yes?

FYI - you can update more than 1 table using a join.
# Posted By Mkruger | 7/15/05 12:36 PM
Jason Nussbaum's Gravatar I believe I've run an update on a view that affects 2 of the underlying tables. But maybe that's just my memory acting up. :) I'll have to check back on that and see if I can find it...

Agreed on the obfuscation - however, with proper documentation it shouldn't actually be an issue. Though proper docs are fairly hard to come by...

Thanks for the info.
# Posted By Jason Nussbaum | 7/15/05 3:07 PM
Miguel Estrada's Gravatar Gracias por tu ayuda!
# Posted By Miguel Estrada | 7/2/08 11:49 AM

Blog provided and hosted by CF Webtools. Blog Sofware by Ray Camden.