I'm looking to do MySQL dumps on a nightly basis. I already have a solution for outputting just a diff of the data, but what I need is a way to import the data back into MySQL so the data in MySQL is UPDATED, instead of INSERTED if it's already there. For example, here's a sample table:
Id | User | Account_Value | Created_At | Updated_At
1 | Bob | $100 | 2009-01-01 | 2009-01-01
2 | Ed | $200 | 2009-01-01 | 2009-01-01
Then the next day, the table looks like this:
Id | User | Account_Value | Created_At | Updated_At
1 | Bob | $50 | 2009-01-01 | 2009-01-02
2 | Ed | $200 | 2009-01-01 | 2009-01-01
When I do the data export on the second day, it will correctly output something like:
INSERT into ACCOUNTS VALUES (1,'Bob', '$50', '2009-01-01', '2009-01-02')
However, this will conflict with what's already there. What I want is to have it overwrite what's already there. Thoughts?
You can add the "on duplicate key update" clause to the insert statements:
http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html