July 8, 2014 at 2:21 pm
I have to tables say 'employee1' and 'employee2'
employee1 has lastname
employee2 has firstname, lastname
i have to update firstname in employee1 from firstname in employee2 table and the common field for both tables
is 'lastname'
Thanks
July 8, 2014 at 2:33 pm
Unless you have other identifying information, all it will take to fail the process is two records in employee2 with the same value for lastname, but differing values for firstname. Until you have a means of matching that goes beyond just last name, or a guarantee that there are never two records in employee2 with the same last name, then you are out of luck. Also, what happens if there are multiple records in employee1 with the same last name, but only one record in employee2 with that last name ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 8, 2014 at 2:40 pm
Steve,
All Last Names are unique
July 8, 2014 at 2:50 pm
This awfully brittle as Steve already said but this should do it.
update e1
set firstname = e2.firstname
from Employee1 e1
join employee2 e2 on e2.lastname = e1.lastname
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 8, 2014 at 2:57 pm
Thank you but i am getting an error saying that "the multi-part identifier could not be bound"
July 8, 2014 at 3:02 pm
Sean's query is exactly what I would code, but not without first running the following query to be absolutely certain that those names are unique:
SELECT COUNT(DISTINCT lastname) AS LAST_COUNT, COUNT(*) AS REC_COUNT
FROM employee2
If you don't see identical numbers then you don't do the update. To identify the dupes:
SELECT lastname, firstname
FROM employee2
WHERE lastname IN (
SELECT lastname
FROM employee2
GROUP BY lastname
HAVING COUNT(*) > 1
)
Hope that helps...
Sean Lange (7/8/2014)
This awfully brittle as Steve already said but this should do it.
update e1
set firstname = e2.firstname
from Employee1 e1
join employee2 e2 on e2.lastname = e1.lastname
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 8, 2014 at 3:11 pm
susmitha117 (7/8/2014)
Thank you but i am getting an error saying that "the multi-part identifier could not be bound"
As posted the query I wrote will work.
create table employee1(firstname varchar(5), lastname varchar(5))
create table employee2(firstname varchar(5), lastname varchar(5))
go
update e1
set firstname = e2.firstname
from Employee1 e1
join employee2 e2 on e2.lastname = e1.lastname
In your actual does you have something else going on. Maybe an incorrect alias? That is probably the most likely culprit.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 8, 2014 at 3:17 pm
Thank you everyone. It worked.
July 8, 2014 at 3:19 pm
You're welcome. Glad that worked for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply