mySQL select differences between two tables in different databases

mysql sql data-comparison
Jason Yost picture Jason Yost · May 31, 2011 · Viewed 16.5k times · Source

I have two databases: old and new.
Both have a comments table. There are 100 comments in the old table that are not in the new. Comments have been added to the new table and there is a conflict in IDs so querying by ID will not be an option. I need so isolate the 100 comments that that they can be exported and inserted into the new database.

I know there are 100 because I have tried using some mysql data compare tools. Unfortunately all of those tools just want to update the comments in the new table with the old content.

Is there a query I can run to get the 100 comments?

Answer

Dave Maple picture Dave Maple · May 31, 2011

Assuming the tables aren't very large you can run something like this:

SELECT *
FROM OldDatabase.CommentTable
WHERE COMMENT NOT IN
    (SELECT COMMENT
     FROM NewDatabase.CommentTable)

Related questions

How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL?

Read More