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:
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).
<cfquery name="getOrd" datasource="#dsn#">
SELECT baseAmt, State
FROM Orders
WHERE ord_id = #val(ord_id)#
</cfquery>
<!--- get the tax rate --->
<cfquery name="getTax" datasource="#dsn#">
SELECT taxRate
FROM stateTaxRate
WHERE State = '#getOrd.state#'
</cfquery>
<!--- get the shipping cost --->
<cfquery name="getShip" datasource="#dsn#">
SELECT shipAmt
FROM ordShip
WHERE ord_id = #val(ord_id)#
</cfquery>
<!--- 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)#
</cfquery>
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.
UPDATE O
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)#
</cfquery>
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.
4 Comments
-
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). -
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. -
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.
Sites to Watch
Aggregators
RSS
Related Blog Entries