August 27, 2008 at 2:52 am
Employee table
EmployeeID Employeename Employeecode Passportcode
1 aaaa 087878
2 bbbb 067678
Passport table
EmployeeId passport code
1 02344
2 02343
How can i update this passport code in the employee table
using update statement any body plz help me.
August 27, 2008 at 2:58 am
Is this homework?
If you let us know what you have tried so far then we can guide you in the correct direction 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 27, 2008 at 3:04 am
update employeetable set passportcode A employeetable, B Passport table
where a.employeeID=b.Employeeid could any give me the correct query the above query is giveing error it will not updated.
August 27, 2008 at 3:08 am
TRy this.
UPDATE e
SET e.passportcode = p.passportcode
FROM Employee e
INNER JOIN Passport p ON p.EmployeeId = e.EmployeeId
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 27, 2008 at 3:26 am
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?
August 27, 2008 at 4:32 am
Hi Guys Thanks A lot
August 27, 2008 at 5:36 am
Rajan John (8/27/2008)
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?
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:
UPDATE Employee
SET passportcocde =
(
    SELECT P.passportcode
    FROM Passport P
    WHERE P.EmployeeId = Employee.EmployeeId
)
WHERE EXISTS
(
    SELECT *
    FROM Passport P1
    WHERE P1.EmployeeId = Employee.EmployeeId
)
August 27, 2008 at 8:15 am
Say, 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 U2
on U1.UserId = U2.UserId
and U2.Type = 'Regular'
amd U1.UserId = '101'
Is there a way that is better on a performance front?
August 28, 2008 at 11:32 pm
I would appreciate any suggestions. Thanks!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply