LinuxQuestions.org
LinuxAnswers - the LQ Linux tutorial section.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 04-18-2006, 04:04 AM   #1
baddah
Member
 
Registered: Feb 2006
Location: Cape Town,South Africa
Distribution: Fedora Core 8
Posts: 183

Rep: Reputation: 30
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
 
Old 04-18-2006, 08:22 AM   #2
graemef
Senior Member
 
Registered: Nov 2005
Location: Hanoi
Distribution: Fedora 13, Ubuntu 10.04
Posts: 2,379

Rep: Reputation: 148Reputation: 148
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!
 
Old 04-19-2006, 12:56 AM   #3
baddah
Member
 
Registered: Feb 2006
Location: Cape Town,South Africa
Distribution: Fedora Core 8
Posts: 183

Original Poster
Rep: Reputation: 30
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
 
Old 04-19-2006, 07:32 AM   #4
graemef
Senior Member
 
Registered: Nov 2005
Location: Hanoi
Distribution: Fedora 13, Ubuntu 10.04
Posts: 2,379

Rep: Reputation: 148Reputation: 148
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.
 
Old 04-20-2006, 12:36 AM   #5
baddah
Member
 
Registered: Feb 2006
Location: Cape Town,South Africa
Distribution: Fedora Core 8
Posts: 183

Original Poster
Rep: Reputation: 30
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).
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
mysql reinstallation distorted by previous tables in /var/mysql mad4linux Linux - Software 0 10-04-2005 01:39 PM
How to compare records in two tables in seperate My Sql database using shell script sumitarun Programming 5 04-14-2005 09:45 AM
Need to compare 2 databases. Help please! morbid_ru Programming 1 12-08-2003 04:16 PM
restoring mysql databases? and mysql permissions... armegeden Linux - Software 0 03-13-2003 11:04 AM
mysql databases k3v0 Linux - Software 2 12-03-2002 07:41 PM


All times are GMT -5. The time now is 04:55 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration