Updating values between two tables

  • 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?

  • 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

  • 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.

  • 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