Need to UPDATE from SELECT

[jymm]

ok - I feel like a fool for asking this, but something is just not connecting today in the brain pan.

I have two different DBs. One that holds the company payroll info and one that holds their time clock data. I want to copy one piece of data from a table in one DB to the other DB.

I have the following SQL:
Declare @TopDate datetime

Select @TopDate = max(PAYPERBEGINNING) from TimeDB..EMPLOYEEBENEFITS

SELECT SOCSCNUM, VACAVLBL
FROM PayrollDB..BasicInfo
where (PayrollDB..BasicInfo.SOCSCNUM in (SELECT TimeDB..EMPLOYEES.SSN
FROM TimeDB..EMPLOYEES INNER JOIN TimeDB..EMPLOYEEBENEFITS ON TimeDB..EMPLOYEES.FILEKEY = TimeDB..EMPLOYEEBENEFITS.FILEKEY
WHERE TimeDB..EMPLOYEEBENEFITS.BENEFIT = 1 And TimeDB..EMPLOYEEBENEFITS.PAYPERBEGINNING = @TopDate))

This gets the info that I was wanting for another reason and I feel that I am close. I want to get another variable from the TimeDB..EMPLOYEES table (var is called Remain) and SET the variable VACAVLBL (in the PayrollDB..BasicInfo database/table) to the value in 'Remain' for each SOCSCNUM.

any suggestions (aside from buy more sugar/caffine) would be appreciated

 

[Morpheus1981]

I would like to help, But I need to know in first place why you didn't just called it in your first select? I mean in this select you call as many variables as you want, for instance:

Select @TopDate = max(PAYPERBEGINNING), @Var2 = AVG(field2)
from TimeDB..EMPLOYEEBENEFITS

But if there's an special reason it will be necesary to know it in order to help you.

 

[jymm]

I love integrating vendor tables...

For the application that I pulled this SQL from the EMPLOYEEBENEFITS table has several records per Socscnum. I want the most recent (max) date. They have things pretty screwy in this table and there are sometimes one record for a ssn/date and sometimes a bunch of SSNs for that date.

could always combine the two I suppose --- just made it cleaner to read back when I wrote that.

thanks