LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Programming (http://www.linuxquestions.org/questions/programming-9/)
-   -   Mysql Compare two tables from different databases (http://www.linuxquestions.org/questions/programming-9/mysql-compare-two-tables-from-different-databases-436294/)

baddah 04-18-2006 04:04 AM

Mysql Compare two tables from different databases
 
Hi,say you've got two databases db1 and db2 and each one has a similar table.The table in db2 has some entries that db1 hasn't(i can get these entries by checking an id field) and i want to insert these entries into the table in db1.

Is there any easy statement to do this?I've tried

>>insert into db1.table select * from db2.table where db2.table.id != db1.table.id ,but it does n't seem to work.

thanks

graemef 04-18-2006 08:22 AM

I think that you want something like:

Code:

INSERT IGNORE INTO table1 (field1, field2, field3)
  SELECT table2.field1, table2.field2, table2.field3
    FROM table2;

The ignore will ignore duplicate keys, The select will take all the values from table2 and the insert will place them into table1.

You will do well to test this first on a temp table!

baddah 04-19-2006 12:56 AM

thanks,that worked!just another question,how do i just list(show) the differences between the two tables without doing any inserts?i've tried select ignore * from table1,table2,does n't work but i assume it must be something like that.thanks

graemef 04-19-2006 07:32 AM

In sql that would be done with the EXCEPT statement which is part of the UNION family of commands. Unfortunately I don't believe that it is supported by MySQL. The command is basically used to join two selects together. MySQL has UNION but no EXCEPT or INTERSECT.

There may be another way, one slightly cumbersome would be to write the two selects to a file and then compare them with a diff command.

baddah 04-20-2006 12:36 AM

thanks,your'e right Mysql does n't support execpt.what's the reason for this?i think its a handy function,but anyway.this seem to work

select distinct id,field1,.. from table1 where id not in (select id,... from table2).


All times are GMT -5. The time now is 05:41 PM.