0

I have a list of name

ID    Name
1     John Doe
2     J. Doe
3     John Doe
4     John Doe

With different methods, I check which names are the same person by checking their actions. For example, in step 1 I find ID 1-3 are one person and ID 4 another person.

Obviously, I need to give each person an ID to group them

ID    Name        PersonID
1     John Doe    1
2     J. Doe      1
3     John Doe    1
4     John Doe    2

In step 2, I find ID 2 and 4 are also the same person (making ID 1-4 the same person). How should I update the parent?

I think I should create PersonID at the last stage. I need a temporary schema to group the records.

Do you any suggestions for a schema to gradually update the same persons.

2 Answers 2

1

In step 2, I find ID 2 and 4 are also the same person (making ID 1-4 the same person). How should I update the parent?

For example, it can be

SET @dup1 := 2;
SET @dup2 := 4;

UPDATE test t0
JOIN test t1
JOIN test t2
SET t0.PersonID = t1.PersonID
WHERE t1.id = @dup1
  AND t2.id = @dup2
  AND t0.PersonID IN (t1.PersonID, t2.PersonID);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=825e1c58b5e7fae31eca9485fa4124ce

1

I would suggest you to have 2 helper tables with following structure

CREATE TABLE NewPersons(
ID INT Identity, 
Name VARCHAR(50)
)

CREATE TABLE PersonMappings(
NewPersonsID INT, 
PersonID INT
)

After 1st step you insert 1 row into NewPersons table with the correct name that you want to use. For example

ID          Name
----------- --------------------------------------------------
1           John Doe

And 3 rows into PersonMappings table which will indicate that the new created person is mapped to Persons with ids 1, 2 and 3.

NewPersonID PersonID
----------- -----------
1           1
1           2
1           3

After you find that person id 2 and 4 are the same, you need to search this ids in your mapping table, and insert that personID with corresponding NewPersonID pair.

SELECT NewPersonID
FROM PersonMappings
WHERE PersonID = 2

You get NewPersonID = 1 from this query and Insert (1, 4) pair into PersonMappings table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.