SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Update statement


Update statement

Author
Message
kumar99ms
kumar99ms
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 550
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.
Christopher Stobbs
Christopher Stobbs
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3214 Visits: 2233
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
Numbers / Tally Tables

SQL-4-Life
kumar99ms
kumar99ms
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 550
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.
Christopher Stobbs
Christopher Stobbs
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3214 Visits: 2233
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
Numbers / Tally Tables

SQL-4-Life
rajankjohn
rajankjohn
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1549 Visits: 575
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?
kumar99ms
kumar99ms
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 550
Hi Guys Thanks A lot
Ken McKelvey
Ken McKelvey
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2228 Visits: 8126
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
)


rajankjohn
rajankjohn
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1549 Visits: 575
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?
rajankjohn
rajankjohn
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1549 Visits: 575
I would appreciate any suggestions. Thanks!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search