InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 19:39:59 GMT20http://www.sqlservercentral.com/Forums/Topic559432-338-1.aspxI would appreciate any suggestions. Thanks!Thu, 28 Aug 2008 23:32:36 GMTrajankjohnhttp://www.sqlservercentral.com/Forums/Topic559432-338-1.aspxSay, I had a wide Users table with 150+ columns. I split that to Users1 and Users2. If I have to update the name column in Users1 with a condition on UserType on Usrs2 table, what I will be doing is as follows -update U1 set U1.Name = 'Sam'from Usrs1 U1 join Usrs2 U2on U1.UserId = U2.UserIdand U2.Type = 'Regular'amd U1.UserId = '101'Is there a way that is better on a performance front?Wed, 27 Aug 2008 08:15:38 GMTrajankjohnhttp://www.sqlservercentral.com/Forums/Topic559432-338-1.aspx[quote][b]Rajan John (8/27/2008)[/b][hr]For such scenarios, I also do the updates with a join only. I would be interested to see whether this approach has any performance implications, and what are other recommended ways?[/quote]While the TSQL update statement can be useful it has the potential problem of not thowing an error if there are multiple results when you were expecting a single result. In this case an employee could have multiple passports so, as there is no guarantee that the last passport will be returned last, the employee row could have the passportcode of an expired passport. Someone should at least ask the question of whether this matters before the TSQL JOIN sysntax is used. If you are expecting Passport.EmployeeId to be unique it could be argued that an UNIQUE constraint on Passport.EmployeeId would solve the problem but can you really rely on that constraint not being removed sometime? In this case I would be inclined to use the ANSI update syntax so that an error would be thrown if the data was not as expected. The performance may be worse than the TSQL JOIN syntax. eg:[code][color="blue"]UPDATE[/color] Employee[color="blue"]SET[/color] passportcocde =[color="gray"]([/color]&nbsp&nbsp&nbsp&nbsp[color="blue"]SELECT[/color] P.passportcode&nbsp&nbsp&nbsp&nbsp[color="blue"]FROM[/color] Passport P&nbsp&nbsp&nbsp&nbsp[color="blue"]WHERE[/color] P.EmployeeId = Employee.EmployeeId[color="gray"])[/color][color="blue"]WHERE[/color] [color="gray"]EXISTS[/color][color="gray"]([/color]&nbsp&nbsp&nbsp&nbsp[color="blue"]SELECT[/color] *&nbsp&nbsp&nbsp&nbsp[color="blue"]FROM[/color] Passport P1&nbsp&nbsp&nbsp&nbsp[color="blue"]WHERE[/color] P1.EmployeeId = Employee.EmployeeId[color="gray"])[/color][/code]Wed, 27 Aug 2008 05:36:23 GMTKen McKelveyhttp://www.sqlservercentral.com/Forums/Topic559432-338-1.aspxHi Guys Thanks A lotWed, 27 Aug 2008 04:32:02 GMTkumar99mshttp://www.sqlservercentral.com/Forums/Topic559432-338-1.aspxFor such scenarios, I also do the updates with a join only. I would be interested to see whether this approach has any performance implications, and what are other recommended ways?Wed, 27 Aug 2008 03:26:42 GMTrajankjohnhttp://www.sqlservercentral.com/Forums/Topic559432-338-1.aspxTRy this.[code]UPDATE eSET e.passportcode = p.passportcodeFROM Employee e INNER JOIN Passport p ON p.EmployeeId = e.EmployeeId [/code]Wed, 27 Aug 2008 03:08:05 GMTChristopher Stobbshttp://www.sqlservercentral.com/Forums/Topic559432-338-1.aspxupdate employeetable set passportcode A employeetable, B Passport tablewhere a.employeeID=b.Employeeid could any give me the correct query the above query is giveing error it will not updated.Wed, 27 Aug 2008 03:04:25 GMTkumar99mshttp://www.sqlservercentral.com/Forums/Topic559432-338-1.aspxIs this homework?If you let us know what you have tried so far then we can guide you in the correct direction :-)Wed, 27 Aug 2008 02:58:42 GMTChristopher Stobbshttp://www.sqlservercentral.com/Forums/Topic559432-338-1.aspxEmployee tableEmployeeID Employeename Employeecode Passportcode1 aaaa 0878782 bbbb 067678Passport tableEmployeeId passport code1 023442 02343How can i update this passport code in the employee tableusing update statement any body plz help me. Wed, 27 Aug 2008 02:52:01 GMTkumar99ms