We have a very repetitive need to execute UPDATE commands in our Database where only one table is updated, but two additional tables need to be referenced. I've been doing SQL for a long time, but have always seemed to just stick with the basics. I've never used JOINs before. Indexes aside, I'm thinking my SQL in general is inefficient and was hoping if someone could let me know if they think Statement 2 below is not only identical to Statement 1 (currently using this format), but is also more efficient?
Any help would be greatly appreciated :)
Anthony Mangione (firstname.lastname@example.org)
To be semantically equivalent, the second query should read
As you surmised, the two UPDATE statements are semantically equivalent (if the latter is rewritten as above). They are also both optimized in exactly the same way - neither will be more efficient than the other.
You can determine the access plan used by either statement by viewing the graphical plan for the statement via DBISQL (SHIFT+F5). To improve performance, appropriate indexes may need to be defined on the columns involved in joining the three tables together, or as well on one or both of COL_2 and COL_6 to efficiently support the comparisons with the string literals "ABC".
answered 11 Nov '10, 21:07