SQL UPDATE - IS there a more elegant and efficient way of doing this?

Question

I have 3 tables Site, Price and PriceMonth. Site has many Prices (joined on siteId). PriceMonth holds a date which is a looked up by Price (joined on PriceMonth).

The idea is that the price records are created when a site is created as place holders.

When a user clicks a Price row in a view - I need to update the prices for the rest of that year only and from that month only.

I've written code for a stored procedure which works. Please ignore the hard coded values for now. It works but can it be made simpler and more efficient?

Code:

DECLARE @startDate smallDateTime         
DECLARE @roc decimal(5,2)
DECLARE @Lec decimal(5,2)
DECLARE @power decimal(5,2)

SET @roc =  (SELECT roc FROM [Price] WHERE siteId = 77 AND PriceMonthId = 527)
SET @lec = (SELECT lec FROM [Price] WHERE siteId = 77 AND PriceMonthId = 527)   
SET @power = (SELECT [power] FROM [Price] WHERE siteId = 77 AND PriceMonthId = 527) 
SET @startDate = (Select [month] FROM  [PriceMonth] WHERE PriceMonthId = 527) 

UPDATE 
    Price
SET 
    roc = @roc
,   lec = @lec
,   [power] = @power
FROM 
    Price
    INNER JOIN priceMonth pm ON price.priceMonthId = pm.priceMonthId

WHERE
   (DATEPART(mm,pm.[Month]) > DATEPART(mm,@startDate)   AND 
   (DATEPART(yy,pm.[Month]) = DATEPART(yy,@startDate))) AND
    price.SiteId = 77

Answer 1

Hello

You can add the variables as joins to the query as follows:

UPDATE     p
SET   roc = sourcePrice.roc,   
      lec = sourcePrice.lec,   
      [power] = sourcePrice.[power]
FROM     Price p
      INNER JOIN [Price] sourcePrice
      on p.siteId = sourcePrice.siteId
      and sourcePrice.siteId = 527
      INNER JOIN priceMonth pm 
      ON price.priceMonthId = pm.priceMonthId
      INNER JOIN priceMonth sourcepm
      ON sourcepm.PriceMonthId = 527

WHERE   pm.[Month] > sourcepm.StartDate
AND     (DATEPART(yy,pm.[Month]) = DATEPART(yy,sourcepm.StartDate))) 
AND     price.SiteId = 77

Also note that I have removed the function from one of your date comparisons - this is so that SQL Server can use any index that might be defined on there to at least narrow the range of values.

Answer 2

You could use multiple assignment to set many variables with the results of one query.

SELECT top 1
  @roc = roc,
  @lec = lec,
  @power = power
FROM Price
WHERE siteId = 77 and pricemonthid = 527

Watch out with this technique:

• if no rows are returned, the variables remain unchanged (null in this case).
• if multiple rows are return, each row is assigned - which leaves the last set of values as the final values. If no ordering was specified, then sql server determines the order of the rows and any row could be last.

---

You could pre-compute the date range that you want to update. This could allow an index on dates to be useful.

DECLARE @StartRange datetime, @EndRange datetime

SET @StartRange = DateAdd(mm, DateDiff(mm, @startDate, 0), 0)
SET @EndRange = DateAdd(yy, 1 + DateDiff(yy, @StartRange, 0), 0)

UPDATE...
WHERE @StartRange <= pm.Month AND pm.Month < @EndRange
  AND price.SiteId = 77

Answer 3

Regarding the requirement for placeholders, you can always use a LEFT JOIN and COALESCE to be able to fall back to the previous month's price. Alternatively, schedule a task to create the new month's prices from the previous month at midnight on the last day of each month.