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.
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 joinUPDATE x SET [APrice]= z.APriceFROM TargetTable xINNER 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 ttSET [APrice]= st.APriceFROM TargetTable ttINNER 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.MaxDateINNER JOIN SourceTable st ON x.[ArtNo] = st.[ArtNo] |
|
|
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.PriceFROM ( 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.ArtNoWHERE RN = 1; |
|
|
|
|
|