Geeks With Blogs
Bunch's Blog One day I'll have a catchy subtitle, one day

Using a CTE to help with an update can be pretty handy. In this simple example the CTE is to select SiteIDs for a specific sales rep. The update is to change the address code to ‘Street’ since the rep entered them all in as ‘Mailing’ by mistake. In the update code you use an inner join to match up the SiteIDs from the CTE with the ones in tblAddress so you only update the specific sales rep’s sites and not all of them in the table.

WITH CTE(SiteID) AS
(
SELECT srs.SiteID
FROM tblSalesRepSites srs
WHERE srs.SalesRepID = '12345'
)

UPDATE tblAddress
SET AddressCode = 'Street'
FROM tblAddress addr INNER JOIN CTE ON addr.SiteID = CTE.SiteID

Technorati Tags:
Posted on Friday, May 27, 2011 12:47 PM | Back to top


Comments on this post: CTE Updating

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Bunch | Powered by: GeeksWithBlogs.net