I have two tables:
Table Employees:
pkID
fkManager //Recursive foreign key. Points back to Employees.pkID
employeeName
TempTable:
employeeName
managerName
This is a fictionnal situation of the problem. Assume employeeName is unique and will never clash.
Now, every columns are filled adequately, except fkManager. I want to take, in the temp table, the name of the employee, find his manager's pkID in the employee table and update the fkManager column adequately.
For example:
Table Employees:
1, NULL, Jim Jones
2, NULL, John Doe
3, NULL, Jane Simmons
4, NULL, Paul Smith
TempTable:
Jim Jones, John Doe
Jane Simmons, John Doe
John Doe, Paul Smith
*** Insert magic query here.... ***
Table Employees:
1, 2, Jim Jones
2, 4, John Doe
3, 2, Jane Simmons
4, NULL, Paul Smith
TempTable:
Jim Jones, John Doe
Jane Simmons, John Doe
John Doe, Paul Smith
I'm running MSSQL Server if that can help.
