SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,935
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Find differences

    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.

    Code:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    rows in A that aren't in B --
    Code:
    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 --
    Code:
    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 --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,935
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks.

  4. #4
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,935
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    'AND', 'OR' or 'USING'

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, my bad, copy/paste error

    change this --
    Code:
        ON b.orb_pos      = a.orb_pos
           b.frequency    = a.frequency
           b.polarisation = a.polarisation
    to this --
    Code:
        ON b.orb_pos      = a.orb_pos
       AND b.frequency    = a.frequency
       AND b.polarisation = a.polarisation
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Keep it simple, stupid! bokehman's Avatar
    Join Date
    Jul 2005
    Posts
    1,935
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •