InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 01:56:55 GMT20http://www.sqlservercentral.com/Forums/Topic932572-392-1.aspxThanks dude. It worked for meMon, 07 Jun 2010 00:35:00 GMTpreetham gowdahttp://www.sqlservercentral.com/Forums/Topic932572-392-1.aspxTry this[code="sql"]Create Table Job (JobID int identity, Role char(10), Location char(50))Create Table Employee (EmpID int identity, EmpName char(50), Role char(10), Location char(50), JobID int)Insert into Job (Role, Location) Values ('SE', 'Bangalore')Insert into Job (Role, Location) Values ('SE', 'Bangalore')Insert into Job (Role, Location) Values ('SE', 'Pune')Insert into Job (Role, Location) Values ('TL', 'Bangalore')Insert into Job (Role, Location) Values ('TL', 'Pune')Insert into Employee (EmpName, Role, Location) Values ('James', 'SE', 'Bangalore')Insert into Employee (EmpName, Role, Location) Values ('Jaks', 'SE', 'Pune')Insert into Employee (EmpName, Role, Location) Values ('Richard', 'SE', 'Delhi')Insert into Employee (EmpName, Role, Location) Values ('Roshan', 'TL', 'Pune')Insert into Employee (EmpName, Role, Location) Values ('Vicky', 'TL', 'Pune')UPDATE EmpSET JobID = Job.JobIDFROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY Role, Location ORDER BY EmpID ) Row, * FROM Employee ) EmpINNER JOIN ( SELECT ROW_NUMBER() OVER ( PARTITION BY Role, Location ORDER BY JobID ) Row, * FROM Job ) Job ON Emp.Role = Job.Role AND Emp.Location = Job.Location AND Emp.Row = Job.RowSELECT * FROM EmployeeDROP TABLE JobDROP TABLE Employee[/code]Fri, 04 Jun 2010 07:11:07 GMTKingston Dhasianhttp://www.sqlservercentral.com/Forums/Topic932572-392-1.aspxIn the following query I need to make it work without WHILE loop.Create Table Job (JobID int identity, Role char(10), Location char(50))Create Table Employee (EmpID int identity, EmpName char(50), Role char(10), Location char(50), JobID int)Insert into Job (Role, Location) Values ('SE', 'Bangalore')Insert into Job (Role, Location) Values ('SE', 'Bangalore')Insert into Job (Role, Location) Values ('SE', 'Pune')Insert into Job (Role, Location) Values ('TL', 'Bangalore')Insert into Job (Role, Location) Values ('TL', 'Pune')Insert into Employee (EmpName, Role, Location) Values ('James', 'SE', 'Bangalore')Insert into Employee (EmpName, Role, Location) Values ('Jaks', 'SE', 'Pune')Insert into Employee (EmpName, Role, Location) Values ('Richard', 'SE', 'Delhi')Insert into Employee (EmpName, Role, Location) Values ('Roshan', 'TL', 'Pune')Insert into Employee (EmpName, Role, Location) Values ('Vicky', 'TL', 'Pune')Set NoCount OnDeclare @intMin int, @intMax intDeclare @intJobID intSelect @intMin = Min(EmpID), @intMax = Max(EmpID) From EmployeeWhile (@intMin <= @intMax)Begin Select @intJobID = Job.JobID From Job Inner Join Employee On Employee.EmpID = @intMin And Job.Role = Employee.Role And Job.Location = Employee.Location Where Not Exists (Select Null From Employee Emp_Tmp Where Emp_Tmp.JobID = Job.JobID) Order By Job.JobID Desc If (@intJobID is not null) Begin Update Employee Set JobID = @intJobID Where EmpID = @intMin End Set @intJobID = null Set @intMin = @intMin + 1EndSelect * From EmployeeDrop Table JobDrop Table EmployeeFri, 04 Jun 2010 05:31:17 GMTpreetham gowdahttp://www.sqlservercentral.com/Forums/Topic932572-392-1.aspxPlease post the table structure, sample data and the desired output in a readily consumable format. I am sure you will get help immediately. Your explanation and example is quite unclear. So, please HELP US TO HELP YOU and have a look at the link in my signature to know how to post to get faster answers. I am sure it will be worth the effort.Thu, 03 Jun 2010 22:58:30 GMTKingston Dhasianhttp://www.sqlservercentral.com/Forums/Topic932572-392-1.aspxI have 2 tables. One with employee location, role, requirement id (to be updated from second table) and second with requirement id, location and role. I need to update employee table with a requirement id. Limitation is that an employee can fit into multiple requirement and vice versa. But I need one to one mapping. For e.g.: I have employee a, b, c and demand 1, 2, 3, 4. I should create mapping like a->2, b->1, c->4. Even though employee - b can fit 1 and 4, but "b" should be either 1 or 4 and not both. Same holds good for demands.Please suggest.Thu, 03 Jun 2010 22:48:54 GMTpreetham gowda