LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Programming (http://www.linuxquestions.org/questions/programming-9/)
-   -   How to compare records in two tables in seperate My Sql database using shell script (http://www.linuxquestions.org/questions/programming-9/how-to-compare-records-in-two-tables-in-seperate-my-sql-database-using-shell-script-313053/)

sumitarun 04-14-2005 12:01 AM

How to compare records in two tables in seperate My Sql database using shell script
 
I have two My Sql Databases. Initially i copy some records from one of the tables from the first database say 'X' to the table in the second database say 'Y'. Now some else comes and modifies some records form that table in database 'X'. Now suppose both the tables in the database has 1000 records. Now How will i determine which records in the table frm\om the database 'X' are modiifed so that i can copy those records to the secod database 'Y'. Can we use some kind of shell script for this?

Note : There is no columns like date time or flag etc in the tables of the first database 'X' which gets updated in case of modifications.

CroMagnon 04-14-2005 01:47 AM

Quote:

There is no columns like date time or flag etc in the tables of the first database 'X' which gets updated in case of modifications.
So... why not add one?

sumitarun 04-14-2005 01:55 AM

Cannot add any columns because it is a ERP database in which large chunk of data are being added by the thrid party and they will not modify the structure. And we have to use the same structure while importing the records to our database.

Harmaa Kettu 04-14-2005 02:16 AM

The easy way: Just copy all the records every time. This is probably good enough solution for a small number of rows like in this case.

The hard way: Dump both tables (sorted) to csv files. Use diff to find differences. Use perl to parse the diff output and generate the SQL statements to update database Y.

CroMagnon 04-14-2005 02:57 AM

Quote:

Cannot add any columns because it is a ERP database in which large chunk of data are being added by the thrid party and they will not modify the structure.
I still don't follow. They don't need to know about a new timestamp column to keep using it exactly as they have, and it would be short work to either add it to your database as well, or strip the timestamp data before inserting or updating. The only way this wouldn't work is if you didn't have administrative control of the database. If that was the case, then this solution also wouldn't work:
You can use mysql's update log to see all insert and update commands on a specific table. Check the documentation for details on that.

Robert G. Hays 04-14-2005 09:45 AM

sumitarun,

I'm guessing that the actual owner of 'X' gave a blanket command that you cannot alter their database in *any* way?

rgh.


All times are GMT -5. The time now is 06:42 AM.