You Dream it...We Build it... 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.
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).
FYI - you can update more than 1 table using a join.
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.