How to combine duplicate parent rows in a relational MySQL table

Question

I have my DB structured like this with people.person_id as the foreign key for each row in people_meta.

people table

 ╔═════╦════════╦═══════╗
 ║ id ║ fname ║ lname ║
 ╠═════╬════════╬═══════╣
 ║ 0 ║ John ║ Doe ║
 ║ 1 ║ John ║ Doe ║
 ║ 2 ║ Bob ║ Smith ║
 ║ 3 ║ Dave ║ Jones ║
 ║ 4 ║ Bob ║ Smith ║
 ║ 5 ║ Jen ║ Smith ║
 ╚═════╩════════╩═══════╝

people_meta table

 ╔═════╦════════════╦════════╦═══════╗
 ║ id ║ person_id ║ key ║ value ║
 ╠═════╬════════════╬════════╬═══════╣
 ║ 0 ║ 0 ║ state ║ NY ║
 ║ 1 ║ 0 ║ age ║ 53 ║
 ║ 2 ║ 1 ║ party ║ D ║
 ║ 3 ║ 1 ║ age ║ 53 ║
 ║ 4 ║ 2 ║ state ║ CA ║
 ║ 5 ║ 4 ║ party ║ R ║
 ╚═════╩════════════╩════════╩═══════╝

What I want to do is combine the duplicate people rows and have it cascade to the people_meta table somehow. Please note that some data in people_meta would be duplicate if they were combined.

The first thing I tried was setting ON DELETE NO ACTION and ON UPDATE CASCADE, then in phpMyAdmin I manually attempted to delete the duplicate parent with the lower ID and update the second duplicate's ID to what the first one was, thinking it would then cascade and give me the desired affect. -FAIL- Apparently using InnoDB's ON UPDATE / ON DELETE features won't be helpful for such a task, but at least I've learned more about their purpose.

I'm sort of a newbie for SQL so I'd appreciate any recommendations.

Answer 1

You'll need to change the people_meta.person_id values before deleting the row from the people table, because the engine wouldn't know how to replace the values with the duplicates.

update people_meta set person_id = 1 where person_id = 0;
delete from people where id = 0;

• user24601: That makes sense. I just made a PHP script that runs a similar update/delete sql combo from my input to assist in completing my task.