-   Programming (
-   -   Mysql Compare two tables from different databases (

baddah 04-18-2006 05: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 != ,but it does n't seem to work.


graemef 04-18-2006 09:22 AM

I think that you want something like:


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 01: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 08: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 01: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 03:41 PM.