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

I think that you want something like:

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!

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

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.

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).