Setting Multiple Fields Quickly and Easily in a SQL Statement

When performing an update on multiple fields you will be temped to do multiple sub queries. For example:

While that code will work, it is very inefficient because SQL must perform behind the scene unions in order to execute the query. If you were setting 100 fields, SQL would execute 101 statements in order for the one query to run. Additionally, this is a maintenance nightmare as the where clause has to be entered/fixed for each subquery.

One final issue with this style is that a coder will typically copy/paste each subquery, and that could cause a rush job to set fields to incorrect values as the programmer could miss a step during creation. While this is an advanced SQL topic, even the beginner should see the above snippet as being messy.
The best-practice to perform this task is to create one sub query and use it as a source. Please look at the following snippet:

Notice the FROM section (outlined in green) above. in this section of the statement you can chose your source table, all of the fields you want to use, and any where clauses you may need. This section resembles the sub queries used in the messy example, but instead selects all fields at once preventing the need for SQL to do union queries. The FROM section above has the alias MySource and is reference in that manner.

Please pay special attention to the where clause (highlighted in yellow). You still need to be able to match up record to record in the update statement. In this design you only need one where clause so there is less confusion and less code to maintain.

*IMPORTANT* Make sure you select the fields from your source query that are required for your join in your where clause.

Also note that you can have extra fields in the source table, so in theory you could say (Select * from MyTable) as MySource but that is considered bad style in SQL programming. It is always better for a developer to spell out the field names unless doing basic queries that will not be saved.

Hope this helps.

Scott

• Bio
• Facebook
• LinkedIn
• Latest Posts

Fermin Iglesias

President at Ubiquity Data

Latest posts by Fermin Iglesias (see all)

• Dedicated Insurance Systems Site Launched - December 16, 2013
• Autonet OEM Chooses UDI - December 7, 2013
• UDI Partners with N-Able - November 19, 2013