April 17, 2008 at 8:20 am
I have a master table that holds all the data that I need to update records as they are placed into another table. I know this isnt all that hard and I have written a script to accomplish the task. My question is that I think there may be a better way to do this.
Here is what I have wrote (and it seems to work)
update Par_Forms set
dept = (select department from PR1_EmployeeMaster where EmployeeNumber = Par_Forms.Employee_ID),
Employee_Status =(select EmployeeStatus_AIT from PR1_EmployeeMaster where EmployeeNumber = Par_Forms.Employee_ID),
EMP_Division = (Select SortField from PR1_EmployeeMaster where EmployeeNumber = Par_Forms.Employee_ID),
Employee_First_Name = (Select FirstName from PR1_EmployeeMaster where EmployeeNumber = Par_Forms.Employee_ID),
Employee_Last_Name = (Select LastName from PR1_EmployeeMaster where EmployeeNumber = Par_Forms.Employee_ID)
I was thinking that I may have a faster update if I could make something llike this work:
DECLARE @EID Int
DECLARE @Dept char(2)
DECLARE @EmpFName VarChar(50)
DECLARE @EmpLName VarChar(50)
DECLARE @FullName VarChar(50)
DECLARE @status VarChar(15)
DECLARE @EMP_Division VarChar(25)
set @EID = (select Employee_ID from Par_Forms)
set @Dept = (select Department from PR1_EmployeeMaster where EmployeeNumber= @EID)
Set @EmpLName = (Select LastName from PR1_EmployeeMaster where EmployeeNumber= @EID)
Set @EmpFName = (Select FirstName from PR1_EmployeeMaster where EmployeeNumber= @EID)
Set @status = (Select EmployeeStatus_AIT from PR1_EmployeeMaster where EmployeeNumber= @EID)
Set @EMP_Division = (Select SortField from PR1_EmployeeMaster where EmployeeNumber= @EID)
Update Par_Forms
set Dept = @Dept,
Employee_First_Name=@EmpFName,
Employee_Status=@Status,
EMP_Division=@EMP_Division
where Employee_ID = @EID
The problem is that the code above doesnt work because it returns multiple values for @EID and it chokes. I was thinking of a loop or something like that, but can't find anything that seems to work. Any advice?
April 17, 2008 at 8:49 am
You can do it with a single Set based operation. You can Update using alias. It can be something like this.
Update a
Set a.dept = b.department,
a.Employee_Status = b.EmployeeStatus_AIT,
a.EMP_Division = b.SortField,
a.Employee_First_Name = b.FirstName,
a.Employee_Last_Name = b.LastName
from Par_Forms as a
JOIN PR1_EmployeeMaster as b
on a.Employee_ID = b.EmployeeNumber
-Roy
April 17, 2008 at 9:05 am
NICE!! Much faster and much cleaner to read. I have to do this for 40 different tables that all reference the same PR1_EmployeeMaster table. You have made my task a little easier.
April 17, 2008 at 9:07 am
I am glad it helped. The best is always try to keep away from Loops or Cursors. Set Based Operation is very powerful and flexible. If you have any condition to check, you can check using CASE Statements
Roy
-Roy
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply