Update query with sub query need top 1 of data

[Kikster813]

I have a table (tblPROREmpMemberStatus)with a foreign ID, code, start date and end date. There can be multiple records per foreign ID.

I want to update another table (tblPREmpDeduductions) with the values from the record from the first table that has the most recent start date.

This is what I'm trying to do:

update tblPREmpDeductions  set ORRetMemberStatus = a.orstatuscode, ORRetMemberStatusBeginDate = a.orstatusbegdate, ORRetMemberStatusEndDate = a.orstatusenddate
FROM (Select Top 1 * FROM tblPROREmpMemberStatus where tblPREmpDeductions.ID = tblPROREmpMemberStatus.tblPREmpDeductionsID Order by ORStatusBegDate DESC) a

And I get the error:
The multi-part identifier "tblPREmpDeductions.ID" could not be bound.

See my attachment for screen shots of tables with data.

 

[bborissov]

SQL 2005 or bigger:

update tblPREmpDeductions 
        set ORRetMemberStatus          = a.orstatuscode,
            ORRetMemberStatusBeginDate = a.orstatusbegdate,
            ORRetMemberStatusEndDate   = a.orstatusenddate
FROM tblPREmpDeductions
INNER JOIN (SELECT tblPREmpDeductionsID,
                   orstatuscode,
                   orstatusbegdate,
                   orstatusenddate,
            ROW_NUMBER() OVER (PARTITION BY tblPREmpDeductionsID ORDER BY ORStatusBegDate DESC) AS RowNumb) a
      ON tblPREmpDeductions.ID = a.tblPREmpDeductionsID AND a.RowNumb = 1

When you alias a table in query (FROM sometable as A or INNER JOIN Table as B) you should use this alias EVERYWHERE in the query.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Kikster813]

I pasted that into my query window and i don't think all the syntax is there. Where would I put the "FROM tblPROREmpMemberStatus" in the sub query?

Thanks for your help so far.

 

[bborissov]

OOPS!!!!!!!!!

update tblPREmpDeductions
        set ORRetMemberStatus          = a.orstatuscode,
            ORRetMemberStatusBeginDate = a.orstatusbegdate,
            ORRetMemberStatusEndDate   = a.orstatusenddate
FROM tblPREmpDeductions
INNER JOIN (SELECT tblPREmpDeductionsID,
                   orstatuscode,
                   orstatusbegdate,
                   orstatusenddate,
            ROW_NUMBER() OVER (PARTITION BY tblPREmpDeductionsID ORDER BY ORStatusBegDate DESC) AS RowNumb
           [COLOR=red]FROM tblPROREmpMemberStatus[/color]) a
      ON tblPREmpDeductions.ID = a.tblPREmpDeductionsID AND a.RowNumb = 1

Sorry

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Kikster813]

Never mind...I figured it out! And it worked perfectly...thanks so much!

update tblPREmpDeductions         
 set ORRetMemberStatus          = a.orstatuscode,            
 ORRetMemberStatusBeginDate = a.orstatusbegdate,            
 ORRetMemberStatusEndDate   = a.orstatusenddate
 FROM tblPREmpDeductions
 INNER JOIN (
	SELECT tblPREmpDeductionsID,orstatuscode,orstatusbegdate, orstatusenddate, ROW_NUMBER()
	OVER (PARTITION BY tblPREmpDeductionsID ORDER BY ORStatusBegDate DESC) AS RowNumb FROM tblPROREmpMemberStatus)   a      
 ON tblPREmpDeductions.ID = a.tblPREmpDeductionsID AND a.RowNumb = 1