mySQL select differences between two tables in different databases

Question

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 1

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)