Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update statement Expand / Collapse
Author
Message
Posted Wednesday, August 27, 2008 2:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 25, 2009 4:57 PM
Points: 74, 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.


Post #559432
Posted Wednesday, August 27, 2008 2:58 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
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


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #559434
Posted Wednesday, August 27, 2008 3:04 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 25, 2009 4:57 PM
Points: 74, 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.
Post #559436
Posted Wednesday, August 27, 2008 3:08 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
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


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #559441
Posted Wednesday, August 27, 2008 3:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:36 AM
Points: 1,199, Visits: 574
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?
Post #559450
Posted Wednesday, August 27, 2008 4:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 25, 2009 4:57 PM
Points: 74, Visits: 550
Hi Guys Thanks A lot
Post #559475
Posted Wednesday, August 27, 2008 5:36 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:40 AM
Points: 845, Visits: 5,445
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
)

Post #559519
Posted Wednesday, August 27, 2008 8:15 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:36 AM
Points: 1,199, Visits: 574
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?
Post #559671
Posted Thursday, August 28, 2008 11:32 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:36 AM
Points: 1,199, Visits: 574
I would appreciate any suggestions. Thanks!
Post #560999
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse