CODEWITHSUNDEEP
mysql
Pete
Pete

Reputation: 21

Comparing 2 identical MySQL tables

I have 2 indentical tables called LIVE and BACKUP.

What I cam trying to do is to compare a LIVE record with its equivalent BACKUP record to see if they match. This check is required each time an individual LIVE record is accessed. i.e. I only want to compare record number 59 (as an example) rather than all records in the LIVE table?

Currently I can do what I want by simply comparing the LIVE record and its equivalent BACKUP record on a field by field basis.

However, I was wondering if it is possible to do a simple "Compare LIVE record A with BACKUP record A".

I don't need to know what the differences are or even in which fields they occur. I only need to know a simple yes/no as to whether both records match or not.

Is such a thing possible or am I stuck comparing the tables on a field by field basis?

Many thanks,

Pete

Upvotes: 2

Views: 73

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Here is a hack, assuming the columns really are all the same:

select count(*)
from ((select *
       from live
       where record = 'A'
      ) union
      (select *
       from backup
       where record = 'A'
      )
     ) t

This will return "1" if they are identical and "2" if more than one record exists. If you want to ensure against two values being in the same table, then use the modified form:

select count(distinct which)
from ((select 'live' as which, l.*
       from live .
       where record = 'A'
      ) union
      (select 'backup' as which, b.*
       from backup b
       where record = 'A'
      )
     ) t;

Also . . . Note the use of union. The duplicate removal is very intentional here.

Upvotes: 1

Related Questions