Mysql Duplicate key / Delete

Question

Basically I am creating a summary table.

The issue is that sometimes the data in the primary table is modified manually. I am using an ON DUPLICATE KEY UPDATE, however I also need something like ON MISSING KEY DELETE. The summary needs to update to the changed data.

Is the best solution really to simply delete all summary records and re-run the INSERT SELECT query? It just doesn't seem like a good idea.

Any keys not in the select query, should not be in the summary table.

Answer 1

i'm not sure i understand, but it sounds like you want triggers on the primary table for INSERT and UPDATE that add to the summary, and another trigger on DELETE that subtracts from the summary...

Answer 2

If the summary query is fast and changes are sporadic, you can just rerun the summary. You might consider using triggers so you remove 1 when deleting, add 1 when inserting etc

Answer 3

After you've populated the summary_table, you could do this:

DELETE s FROM summary_table s LEFT OUTER JOIN original_table o ON s.id = o.id
WHERE o.id IS NULL;

That will remove from summary_table any rows where the id no longer exists in the original_table.

I don't think there's any way you can do this in one statement.