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:
Here's what I see more often than not (forgive me if I do not use CFQUERYPARAM. I'm saving space on the page).
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.
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.