CODEWITHSUNDEEP
phpmysql
Elitmiar
Elitmiar

Reputation: 36879

Comparing 2 mysql tables and updating only records that have changed

I have the following. Two mysql tables. I want to copy info that has changed from table a to b.

For example if row 1 column 2 has changed in table a I want to only update that column in table b. Table b is not the same as a but has same columns that also exist in a. The other solution I have is to just clear table b and replace it with the contents from table a, the problem with this could be that the script will take longer to execute, since there are more than 10000 records. Any advise for which method would work the best will be highly appreciated

Upvotes: 2

Views: 942

Answers (2)

symcbean
symcbean

Reputation: 48387

If you've got the same data in 2 different tables on the same database, then your database is not normalized - you're just compounding your difficulties by finding workarounds for the bad structure.

If these were 2 seperate databases - say distributed nodes on a cluster (although this implies bi-directional synchronization) or where you have an near-online copy of the database for reporting purposes, then you should still normalize your data properly and move the fields not present in table 'a' but found in table 'b' to a third table. The to address the population/replication issue, either:

1) use the builtin replication functionality in MySQL

2) add an indexed timestamp field to table 'a' then merge the records that have been updated since some time T

Note that the second method doesn't propogate deletions.

C.

Upvotes: 0

Haim Evgi
Haim Evgi

Reputation: 125594

use trigger on update one table that update another. can see example in this tutorial

http://www.brainbell.com/tutorials/MySQL/Creating_Triggers.htm

and in this post

http://forums.mysql.com/read.php?99,282455,282559#msg-282559

Upvotes: 2

Related Questions