Update query - based on non matching criteria

[dcorleto]

I need to update employee ID numbers into a table from data in another table. The table with the employee ID's have first and last name fields. The table that needs updating has the same field names, but the naming convention is different. For example, one table has Doe, John while the other has Doe, John M. How can I make each one recogize the other, if the names are different perhaps by a middle initial or suffix - is this possible.

Thanks

 

[Remou]

With some work it may be possible to get a certain level of matching, but I think you will have to do a certain number of updates by hand.

 

[rac2]

It will be hit-or-miss. I would move the data in stages, starting with strong matching criteria, and move to weaker criteria. At each stage, first do a SELECT to see that the rows you wish to change are the only ones retrieved.

First match "Doe, John" and "Doe, John"
This is a strong match.
Check with

SELECT o.empid,
       o.firstname, n.firstname,
       o.lastname, n.firstname
FROM MyNewTable n
JOIN MyOldTable o ON o.firstname = n.firstname
                 AND o.lastname = n.lastname
WHERE n.empid IS NULL

There should be no duplicates for the o.empid. If there are then you have two people with the same name.

If it looks good then

UPDATE MyNewTable SET empid = o.empid
FROM MyNewTable n
JOIN MyOldTable o ON o.firstname = n.firstname
                 AND o.lastname = n.lastname
WHERE n.empid IS NULL

Then match partially. For example

SELECT o.empid,
       o.firstname, n.firstname,
       o.lastname, n.firstname
FROM MyNewTable n
JOIN MyOldTable o ON o.firstname LIKE n.firstname & "*"
                 AND o.lastname = n.lastname
WHERE n.empid IS NULL

Here the match is based on the new firstname being the beginning pieces of the old firstname. That is, matches new "John" to old "John M", "John J", "John", "John t", etc.

The WHERE clause limits the damage, excuse me, the changes to rows which have not yet been matched.

In the end it may be necessary to manually decide which records match and enter the id.