locked
Query to update master table data from other tables based on foreignkeys RRS feed

  • Question

  • User136609187 posted

    I have the below 3 tables in DB.
    EmployeeMasterData is the master table and it has foreign keys DesignationId & DepartmentId from EmployeeDesignation and EmployeeDepartment.
    The problem is i need to update this EmployeeMasterData from a temporary table and this table has only Designation & Department.So need to fetch corresponding
    DesignationId & DepartmentId and update EmployeeMasterData.

    EmployeeMasterData

    EmpId | FName |LName | DesignationId

    EmployeeDesignation

    DesignationId | Designation

    EmployeeDepartment

    DepartmentId | Department


    Iam doing a bulk copy from excel to a temporary table and from this table i need to update EmployeeMasterData.So in this temporary table I have the below columns

    EmpId Department Designation Dob ... etc and all other data present in EmployeeMasterData expect the foreign keys DepartmentId & DesignationId.

    From this temporary table I have to update the EmployeeMasterData.

    How can i get the DepartmentId and DesignationId from the EmployeeDepartment and EmployeeDesignation master table based on

    each EmpID and update the DepartmentId & DesignationId on EmployeeMasterData table for all employees.


    Please help me out with a sample sql query

    Thursday, March 3, 2016 4:39 AM

All replies

  • User-1768369891 posted

    HI 

    chithra_iyer

    Try this if your user define table is @tbl which having columns likes EmpId Department Designation Dob. 

     Update EmployeeMasterData 

     SET DesignationId = Isnull(Ed.DesignationId,EmployeeMasterData.DesignationId)   

    From @tbl as temptable 

    Left Join EmployeeDesignation as Ed 

    on Lower(Rtrim(Ltrim(Ed.Designation))) = Lower(Rtrim(Ltrim(temptable.Designation)))

    Where temptable.empid = EmployeeMasterData.empid  

    and you not define DOB and department reference in EmployeeMasterData 

    if i'm right then EmployeeMasterData have reference of both.  

    Thursday, March 3, 2016 5:07 AM