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

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

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

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

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

Advertisements
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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: