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.
Best Answer
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.