log in

Complex joins in update statements

Luke Breuer
2007-04-20 18:07 UTC

When performing updates, complex joins should be moved to subqueries for optimal performance. Complex is probably defined as anything other than a simple "on a = b" condition.

In the case of subqueries that won't always return results, consider performing the update like this:
update  the_table set
        the_field = isnull((
            select  other_field
            from    other_table
            where   a = b or
                    c = d
        ), the_field)
This appears to be much faster than repeating the subquery in the where clause using exists or is not null.

Alternatively, the problem may be that the join is not 1:M, updated:joined table, which would result in the table being updated getting updated multiple times. A subquery without "select top 1" in it in the same situation would raise an error when multiple results are returned, which is a good thing.