UPDATE dbo.mp_RPINSpecifications
SET SpecpacksPerMinute = mp_RPINUpload.PPM
WHERE mp_RPINUpload.Reource = WITH CTE AS
				(
				SELECT --use the row_number function to get the newest record
				    *, ROW_NUMBER() OVER(PARTITION BY s.SpecRecordID_rc ORDER BY  s.SpecIssueNo DESC) AS seq
				FROM dbo.mp_RPINSpecifications s (NOLOCK)
				)
				SELECT  s.SpecID,
					s.SpecRecordID_rc,
					r.RecordID,
					r.Resource,
					s.SpecIssueNo,
					s.SpecpacksPerMinute				
				FROM CTE s (NOLOCK)
				INNER JOIN mp_rcResourceData r (NOLOCK) ON s.SpecRecordID_rc = r.RecordID
				WHERE seq = 1 --filter for the newest record only
				ORDER BY r.Resource

CREATE TABLE #CTE
    (
      [SpecRecordID_rc] BIGINT NULL ,
      [SpecIssueNo] INT NULL ,
      [Resource] [varchar](35) NULL ,
      [PPM] FLOAT NULL
    )

WITH    CTE
          AS ( SELECT
                        * ,
                        ROW_NUMBER() OVER ( PARTITION BY s.SpecRecordID_rc ORDER BY s.SpecIssueNo DESC ) AS seq
               FROM     dbo.mp_RPINSpecifications s ( NOLOCK )
             )
    INSERT  INTO #CTE
            SELECT  CTE.SpecRecordID_rc ,
                    CTE.SpecIssueNo ,
                    r.Resource ,
                    u.PPM
            FROM    CTE (NOLOCK)
                    INNER JOIN mp_rcResourceData r ( NOLOCK ) ON CTE.SpecRecordID_rc = r.RecordID
                    INNER JOIN mp_RPINUpload u ( NOLOCK ) ON r.Resource = u.Resource
            WHERE   seq = 1
            ORDER BY CTE.SpecRecordID_rc


UPDATE  dbo.mp_RPINSpecifications
SET     SpecpacksPerMinute = #CTE.PPM
FROM    dbo.mp_RPINSpecifications spec
        INNER JOIN #CTE ON spec.SpecRecordID_rc = #CTE.SpecRecordID_rc
                           AND spec.SpecIssueNo = #CTE.SpecIssueNo
WHERE   spec.SpecRecordID_rc IN ( SELECT    [SpecRecordID_rc]
                                  FROM      #CTE )
        AND EXISTS ( SELECT 1
                     FROM   mp_RPINUpload
                            INNER JOIN #CTE ON dbo.mp_RPINUpload.Resource = #CTE.Resource
                                               AND #CTE.SpecRecordID_rc = spec.SpecRecordID_rc
                                               AND #CTE.SpecIssueNo = spec.SpecIssueNo )                         

DROP TABLE #CTE