Update table 1 data with top 1 data of other table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Update table 1 data with top 1 data of other table

Author

Topic

PeetKoekemoer
Starting Member

12 Posts

Posted - 2012-04-23 : 13:33:52

I have 2 tables performancemanagement and employees. The performamcemanagement contains detail of employees, I want to update each employee's supervisors data with the data from the employees table, but sometimes a employee will report to more that one supervisor but I want to select the first supervisor in the list that group employee report's to and update all their fields.

Below is the data:
PerformanceManagement Table
Hierarchy ProdMonth BeginDate EndDate Activity IndustryNo OrgUnit Name Surname Designation PatGrade LineIndustryNo LineOrgUnit LineName LineSurname LineDesignation LinePatGrade
PS 201203 15/02/2012 14/03/2012 0 A3207537 KK0115A ARMINDO RICARDO Supervisor Operations C4 KK0115
PS 201203 15/02/2012 14/03/2012 0 A3590831 KK0115C THOMAS MANAKE Supervisor Operations C4 KK0115
PS 201203 15/02/2012 14/03/2012 0 A2633565 KK0115EN VUSIE GININDZA Supervisor Operations C4 KK0115
PS 201203 15/02/2012 14/03/2012 0 A3639358 KK0115B KINGDOM MATEKANE Supervisor Operations C4 KK0115
PS 201203 15/02/2012 14/03/2012 0 A3597282 KK0115E JEROME ZULU Supervisor Operations C4 KK0115

employees table
OrgUnit IndustryNo Name Surname Designation RateCategory
KK0115 A3794082 Obakeng Galeboe Mine Overseer Operations D-Lower
KK0115 M3002519 TSOENYANE MABESA Miner Stoper C1

below is my script:
Update Perf set
LineIndustryNo = emp.IndustryNo,
LineName = emp.Name,
LineSurname = emp.Surname,
LineDesignation = emp.Designation,
LinePatGrade = emp.RateCategory
from PerformanceManagement Perf,
(Select top 1 Employees.OrgUnit, Employees.IndustryNo, Employees.Name, Employees.Surname, Employees.Designation, Employees.RateCategory from Employees
inner join PerformanceManagement on Employees.OrgUnit = PerformanceManagement.LineOrgUnit
) emp
Where
Perf.ProdMonth = 201203 and
Perf.Activity = 0 and
Perf.Hierarchy = 'PS' and
Perf.OrgUnit like ('KK0115%')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 15:10:39

[code]
Update Perf set
LineIndustryNo = emp.IndustryNo,
LineName = emp.Name,
LineSurname = emp.Surname,
LineDesignation = emp.Designation,
LinePatGrade = emp.RateCategory
from PerformanceManagement Perf
cross apply
(Select top 1 Employees.OrgUnit, Employees.IndustryNo, Employees.Name, Employees.Surname, Employees.Designation, Employees.RateCategory from Employees
where Employees.OrgUnit = Perf.LineOrgUnit
) emp
Where
Perf.ProdMonth = 201203 and
Perf.Activity = 0 and
Perf.Hierarchy = 'PS' and
Perf.OrgUnit like ('KK0115%')
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

PeetKoekemoer
Starting Member

12 Posts

Posted - 2012-04-24 : 01:23:41

Thanx you cross apply worked

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-24 : 01:25:36

welcome

see what all you can do with it

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

PeetKoekemoer
Starting Member

12 Posts

Posted - 2012-04-24 : 02:32:52

THANX

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-24 : 21:40:35

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources