Updating Table with values from another table ?
-
Hi Team, Not quite new but not experienced to do this!
I have a table with payroll numbers which need to be updated from another table. There is not a unique key as I have to use last name, of which may be more than one.
In primary table I hold First Name, Last Name and payroll number which may be null or populated with a number.
Abdillah Hassan NULL(Employee No)
In table that imports data I have Last Name and Initial.
F501361 Mr H Hassan
Any ideas on how to update all payroll numbers to correct name.
Steve
-
UPDATE p
SET p.EmployeeNo = i.EmployeeNo
FROM [primary] p
INNER JOIN [import] i
ON i.LastName = p.LastName
AND i.Initial = LEFT(p.FirstName,1)
WHERE p.EmployeeNo IS NULL
BUT BEWARE! This will match Jim Bloggs, Joe Bloggs, Jack Bloggs, Jill Bloggs to 'Bloggs J'
To stop this you need more differentiation
Far away is close at hand in the images of elsewhere.
Anon. -
first of all you're going to have to identify all of the duplicate surnames in your primary table and also all of the equivalents in your import table.
if you set up a new field on the primary table as BIT called DUPLICATE and mark it as 1 for all records where there are duplicate employees (such as John jones and Jake Jones)
now all you have to do is run the following
declare @surname varchar(50)
declare @firstname varchar(50)
declare @payroll varchar(50)
declare curs1 cursor for select surname,firstname,payroll from primarytable where DUPLICATE=0
open curs1
fetch next from curs1 into @surname,@firstname,@payroll
while @@fetch_status=0
begin
update primarytable set payroll=(select payroll from secondarytable where initial=left(1,@firstname) and surname=@surname)
fetch next from curs1 into @surname,@firstname,@payroll
end
close curs1
deallocate curs1
the reason for soing it in a cursor is so that you can add any other business logic you need - such as "IF isnull(@payroll)" etc
this isn't the only way, but it'll work quite easily
MVDBA
-
Thank you for replies all.
I have managed to get full first names now and tried David method.
As I had set up a DupMail bit for another reason I used this also as it was set to 0 as default I changed your script to use 1 as flag.
When I run the script below it updates all the rows regardless of what the query above returns! I set up a dummy user called Fred Dibner and it only returns his row when the first part is run.
declare @LastName varchar(50)
declare @FirstName varchar(50)
declare @payroll varchar(50)
declare curs1 cursor for select Inf_LastName,Inf_FirstName,Inf_EmployeeNo from VT_MailInfo where Inf_DupMail=1
open curs1
fetch next from curs1 into @LastName,@FirstName,@payroll
while @@fetch_status=0
begin
update VT_MailInfo set Inf_EmployeeNo=(select Pers_EmployeeNo from VT_PersonelData where Pers_FirstName=@FirstName <mailto
ers_FirstName=@FirstName> and Pers_LastName=@LastName <mailtoers_LastName=@LastName>)fetch next from curs1 into @LastName,@FirstName,@payroll
end
close curs1
deallocate curs1
What have I done wrong ?
Help
Steve
-
Thank you for replies all.
I have managed to get full first names now and tried Mikes method.
As I had set up a DupMail bit for another reason I used this also as it was set to 0 as default I changed your script to use 1 as flag.
When I run the script below it updates all the rows regardless of what the query above returns! I set up a dummy user called Fred Dibner and it only returns his row when the first part is run.
declare @LastName varchar(50)
declare @FirstName varchar(50)
declare @payroll varchar(50)
declare curs1 cursor for select Inf_LastName,Inf_FirstName,Inf_EmployeeNo from VT_MailInfo where Inf_DupMail=1
open curs1
fetch next from curs1 into @LastName,@FirstName,@payroll
while @@fetch_status=0
begin
update VT_MailInfo set Inf_EmployeeNo=(select Pers_EmployeeNo from VT_PersonelData where Pers_FirstName= @FirstName and Pers_LastName=@LastName )
fetch next from curs1 into @LastName,@FirstName,@payroll
end
close curs1
deallocate curs1
What have I done wrong ?
Help
Steve
-
update VT_MailInfo set Inf_EmployeeNo=(select Pers_EmployeeNo from VT_PersonelData where Pers_FirstName= @FirstName and Pers_LastName=@LastName and inf_DUP_MAIL=1<-----THIS BIT HERE ???)
MVDBA
-
I may have confused myself and you on tables names.
I am calling the primary table my mail info which is the table that needs to have the payroll number updated.
The personnel table is the on which holds the correct payroll number.
I created/had the bit set in the mail info table!
So Mike are you saying it should have gone into the payroll table
Steve
-
Mike,
The code below will update the correct rows but will reset those not marked with dup bit set to NULL!
Steve
-
what i meant was that you are running the update against ALL of employees without checking to see if they have the DUP flag set -
your cursor picks up the DUP flag, but you don't perform the update using the DUP flag ...
MVDBA
-
This is code which updates correct row but also resets all others to NULL
Steve
declare @LastName varchar(50)
declare @FirstName varchar(50)
declare @payroll varchar(50)
declare curs1 cursor for select Inf_LastName,Inf_FirstName,Inf_EmployeeNo from VT_MailInfo where Inf_DupMail=1
open curs1
fetch next from curs1 into @LastName,@FirstName,@payroll
while @@fetch_status=0
begin
update VT_MailInfo set Inf_EmployeeNo=(select Pers_EmployeeNo from VT_PersonelData where <A href="mailto
ers_FirstName=@FirstName">Pers_FirstName=@FirstName and <A href="mailtoers_LastName=@LastName">Pers_LastName=@LastName and Inf_DupMail=1)fetch next from curs1 into @LastName,@FirstName,@payroll
end
close curs1
deallocate curs1
-
oops just spotted your mistake
update VT_MailInfo set Inf_EmployeeNo=(select Pers_EmployeeNo from VT_PersonelData where Pers_FirstName=@FirstName and Pers_LastName=@LastName and Inf_DupMail=1) WHERE ......
MVDBA
-
Thanks for all your help Mike, that seems to work on my small test. Will try it out on a few more records to make sure.
Thank you again
Steve
-
no problem -
although looking at this in retrospect i think we could have done a lot better.
at least it will work though!!
MVDBA
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply