Update … From

In the past I have quite often wanted to update values in a table based on values in another table (esp for caching data in DataWarehouses / migrating data between databases). Rather than writing a stored procedure to iterate through both tables, a much more efficient way is to use the From clause in an Update statement (NB: this is vendor specific, in this case, SQL Server). I was walking one of the junior developers through this today – basically what you need to do is start with a select statement creating the relationship between the two (or more) tables you want to combine

      count(distinct r.requestID)
      customerRequests r,
      summaryTable st
      r.customerID = st.customerID

Then, you just need to change it so that it is an update statement

      customerRequests = count(i.requests) — Put all the sets here…
      customerRequests i, 
      summaryTable st — Defines relationship
      r.customerID = st.customerID

Of course, you can make this more complex (in our case we had three tables updating)

Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: