Find differences

Databases
1

How do I find the difference between two tables with identical structures?

I’ve got two tables (about 7000 lines) and once a week I want to run a query that finds the additions, modifications and delitions of table ‘A’ compared to table ‘B’. Table structure below. In the case of a modification 3 fields always will remain identical, and one or more of the other parameters will change. The 3 fields are orb_pos, frequency, polarisation. Could possibly add a primary key for these fields.


CREATE TABLE IF NOT EXISTS `transponders` (
  `orb_pos` smallint(11) NOT NULL,
  `frequency` smallint(11) UNSIGNED NOT NULL DEFAULT '0',
  `symbol_rate` smallint(11) UNSIGNED NOT NULL DEFAULT '0',
  `polarisation` tinyint(4) UNSIGNED NOT NULL DEFAULT '0',
  `fec_inner` tinyint(4) UNSIGNED DEFAULT NULL,
  `DVB_system` tinyint(4) UNSIGNED DEFAULT NULL,
  `modulation` tinyint(4) UNSIGNED DEFAULT NULL,
  `feed` tinyint(4) UNSIGNED DEFAULT NULL,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
2

rows in A that aren’t in B –

SELECT a.orb_pos
     , a.frequency
     , a.polarisation
  FROM tableA AS a
LEFT OUTER
  JOIN tableB AS b
    ON b.orb_pos      = a.orb_pos
       b.frequency    = a.frequency
       b.polarisation = a.polarisation
 WHERE b.orb_pos IS NULL

rows in B that aren’t in A –

SELECT b.orb_pos
     , b.frequency
     , b.polarisation
  FROM tableB AS b
LEFT OUTER
  JOIN tableA AS a
    ON a.orb_pos      = b.orb_pos
       a.frequency    = b.frequency
       a.polarisation = b.polarisation
 WHERE a.orb_pos IS NULL

rows which have changed data –

SELECT a.orb_pos
     , a.frequency
     , a.polarisation
  FROM tableA AS a
INNER
  JOIN tableB AS b
    ON b.orb_pos      = a.orb_pos
       b.frequency    = a.frequency
       b.polarisation = a.polarisation
 WHERE b.symbol_rate <> a.symbol_rate
    OR b.fec_inner   <> a.fec_inner
    OR b.DVB_system  <> a.DVB_system
    OR b.modulation  <> a.modulation
    OR b.feed        <> a.feed
3

Thanks.

4

Those queries are raising errors.

Query 1 and 2: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘b.frequency = a.frequency b.polarisation = a.polarisation WHERE b.orb’ at line 8

Query 3: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘b.frequency = a.frequency b.polarisation = a.polarisation WHERE b.sym’ at line 8

5

‘AND’, ‘OR’ or ‘USING’

6

yeah, my bad, copy/paste error

change this –

    ON b.orb_pos      = a.orb_pos
       b.frequency    = a.frequency
       b.polarisation = a.polarisation

to this –

    ON b.orb_pos      = a.orb_pos
   AND b.frequency    = a.frequency
   AND b.polarisation = a.polarisation
7

Thanks.