locked
Migrating data from one table to another RRS feed

  • Question

  • I have a documentation table which contains a row for many items related to employees, including date of birth (dob)

    I'd like to remove the dob records and store the dob in the Employees table.

    Is there a nifty way I can do this in a single query?

    something like:

    update Employees set dob=(select EffectiveDate, EmployeeID as EID from EmployeeDocumentation where 

    DocumentTypeID=1) where EmployeeID = EID

    for each employee?

     


    • Edited by naht Thursday, June 23, 2011 11:49 PM better example
    Thursday, June 23, 2011 11:47 PM

Answers

  • Update Employees 
    Set dob = SubQry.EffectiveDate 
    From Employees 
    Inner Join EmployeeDocumentation As SubQry On Employees.EmployeeId = SubQry.EmployeeId And SubQry.DocumentTypeId = 1
    


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    • Marked as answer by naht Friday, June 24, 2011 3:07 AM
    Thursday, June 23, 2011 11:54 PM

All replies

  • Update Employees 
    Set dob = SubQry.EffectiveDate 
    From Employees 
    Inner Join EmployeeDocumentation As SubQry On Employees.EmployeeId = SubQry.EmployeeId And SubQry.DocumentTypeId = 1
    


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    • Marked as answer by naht Friday, June 24, 2011 3:07 AM
    Thursday, June 23, 2011 11:54 PM
  • Hi

    check with this

     

    Update Employees set dob = ed.EffectiveDate

     From Employees e Inner Join EmployeeDocumentation ed On e.EmployeeID = ed.EmployeedID where ed.DocumentTypeID = 1

     


    If this post answers your question, please click "Mark As Answer". If this post is helpful please click "Mark as Helpful".
    Thursday, June 23, 2011 11:54 PM
  • Hi naht

    you can use this

    Update Employees set dob = ed.EffectiveDate

     From Employees emp Inner Join EmployeeDocumentation ed On

     emp.EmployeeID = ed.EmployeedID where ed.DocumentTypeID = 1


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Friday, June 24, 2011 12:40 AM