steve Kim steve Kim - 1 month ago 7
MySQL Question

syncing two mysql db table

So, I have two mysql db tables: "table_original" and "table_copy"

Here is my current setup:

Every night,

table_original
gets updated from another server with information. It either removes the old db row or adds a new line it with unique post_id (there won't be no two identical post_id even if row is removed).

Then, certain information along with the post_id is copied into
table_copy
. Thus two are linked by the
post_id
.

So far, the
table_original
has ~20,000 rows while
table_copy
has ~40,000.

I just noticed that
table_copy
has not been being updated from the first table but only have been adding rows instead of removing anything that was removed from the first table.

In order to sync them, my initial approach was to check each row from
table_original
against the
table_copy
then if post_id exists, then do nothing, if not, remove the row from the
table_copy
.

My concern is that, the
table_original
will get updated every night: either older post_id row will be removed and/or add new rows. Unfortunately there is no way for me to know what is being done before it is done. Meaning, I have to wait till the db is updated to see what was removed and added.

Then, every time the first table is updated, then I have to check every rows against the second table to update them. The table will only get bigger and I am concerned that this approach might not be the best.

What do you guys suggest that I can do?

Thanks!

Answer

To delete the rows in table_copy that are not present in table_original:

DELETE t1.* FROM table_copy AS t1 
LEFT OUTER JOIN table_original AS t2 USING (post_id)
WHERE t2.post_id IS NULL;

I recommend you create a dummy table with a few rows so you can experiment with this kind of query and increase your confidence with it before you run it against your real data!

See http://dev.mysql.com/doc/refman/5.7/en/delete.html for more docs on the multi-table DELETE statement.

To keep them in sync every night automatically, use triggers:

CREATE TRIGGER copy_on_ins AFTER INSERT ON table_original
FOR EACH ROW
  INSERT INTO table_copy SET post_id = NEW.post_id, other_columns = NEW.other_column;

CREATE TRIGGER copy_on_upd AFTER UPDATE ON table_original
FOR EACH ROW
  UPDATE table_copy SET other_column = NEW.other_column
  WHERE post_id = NEW.post_id;

CREATE TRIGGER copy_on_del AFTER DELETE ON table_original
FOR EACH ROW
  DELETE FROM table_copy WHERE post_id = OLD.post_id;

See http://dev.mysql.com/doc/refman/5.7/en/create-trigger.html

Comments