Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 update value of latest record

Author  Topic 

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2013-02-27 : 17:28:00
I have a table with product prices which contain an entry for each time my products had a price change with a start date and and end date.
I would like to update the price of the last entry for each of my products, so I determine the MAX start date to get the record that needs to be updated. My values for updating are in another table to which I join


UPDATE x SET [APrice]= z.APrice
FROM TargetTable x
INNER JOIN
(SELECT [ArtNo], MAX([Date]) AS MaxDate
FROM x
GROUP BY [ArtNo]) y ON x.[ArtNo] = y.[ArtNo] AND y.MaxDate = x.[Date]
INNER JOIN SourceTable z ON x.[ArtNo] = z.[ArtNo]


Is there a more efficient way to do this? I have ~ 1 million records in my target table and this is taking a very long time to run.
Any help is appreciated.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-02-27 : 18:34:46
Ultimately performance may depend on how the tables are indexed. In particular, have an index on ArtNo in the SourceTable.

At any rate, you can filter the ArtNo's earlier in the process, hopefully reducing the processing overhead, something like this:


UPDATE tt
SET [APrice]= st.APrice
FROM TargetTable tt
INNER JOIN
(SELECT x2.[ArtNo], MAX(x2.[Date]) AS MaxDate
FROM x2
INNER JOIN SourceTable st2 ON x2.[ArtNo] = st2.[ArtNo]
GROUP BY x2.[ArtNo]
) AS y ON tt.[ArtNo] = y.[ArtNo] AND tt.[Date] = y.MaxDate
INNER JOIN SourceTable st ON x.[ArtNo] = st.[ArtNo]
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-27 : 19:16:18
Another way of doing the same thing - not sure if this will be any faster than Scott's method though:
UPDATE c SET
APrice = z.Price
FROM
(
SELECT
ArtNo,
APrice,
ROW_NUMBER() OVER (PARTITION BY ArtNo ORDER BY [Date] DESC) AS RN
FROM
TargetTable
) c
INNER JOIN SourceTable z ON
z.ArtNo = c.ArtNo
WHERE
RN = 1;
Go to Top of Page
   

- Advertisement -