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.
Related Blog Entries