SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 update value of latest record
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

HenryFulmer
Posting Yak Master

USA
110 Posts

Posted - 02/27/2013 :  17:28:00  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
383 Posts

Posted - 02/27/2013 :  18:34:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 02/27/2013 :  19:16:18  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000