Uniqe Replace Query

Question

I have table1 that has full lastname and full firstname. I have table2 that has full lastname and just the first letter of the first name in the firstname field.

I want to replace table2 firstname with the fristname in table1. The problem is that in both tables there are several people with the same last name.

The id in both tables are different and won't match. Any way to relate table1 firstname with table2 firstname with a replace query?

If you had a pen and paper, could you do that manually? – Amit Aug 08 '15 at 22:08 — Amit, Aug 08 '15 at 22:08

score 0 · Accepted Answer · edited May 23 '17 at 10:29

Exactly how to do it depends on the DBMS you are using, but I think something like this should do the trick. Inspired by answers to this question.

UPDATE table2
SET table2.firstname = table1.firstname
FROM table1, table2
WHERE
    table1.lastname = table2.lastname AND 
    table1.firstname LIKE CONCAT(table2.firstname, '%')

The WHERE conditions finds a match in table1 that has the same lastname as in table2, and whos firstname begins with the same string. CONCAT is string concatenation, so you would get something looking like 'Bobby' LIKE 'Bob%'.

Please note, that if there are several matches for one row in table2 (for instance, both Anna Smith and Anastasia Smith matching An... Smith), that row will be updated with both. The last one will be the one who sticks, but which one who happend to be last is pretty much random. To check if you have any cases like that, I think you could run this query:

SELECT table2.firstname, table2.lastname
FROM table1, table2
WHERE
    table1.lastname = table2.lastname AND 
    table1.firstname LIKE CONCAT(table2.firstname, '%')
GROUP BY table2.firstname, table2.lastname
HAVING COUNT(*) > 1

Disclaimar: I have not tested any of this.

score 0 · Answer 2 · answered Aug 08 '15 at 23:10

First check whether you have duplicate combinations in table2:

SELECT lastname, name, count(*)
FROM table2
GROUP BY lastname, name
HAVING count(*) > 1

If you don't have duplicates in table2 solution is easy, you can join tables like this:

... t1.lastname = t2.lastname 
   AND SUBSTRING(t1.name, 1, 1,) = t2.name

As exact db is not specified in the question, I am skipping the complete UPDATE query here.

If first query returns duplicates, you will need to deal with those. If you update those records first with appropriate names, you will be able to run the update query, as it will only affect records where name is single char in table2.

Uniqe Replace Query

2 Answers2