CODEWITHSUNDEEP
databasediffmysqldump
MJQ
MJQ

Reputation: 1786

Get the difference of two sql files

I want to get the difference of two sql dumps. I have to save the difference in a new file. The difference should be legitimate query! For example, If I have a file with following content,

CREATE TABLE IF NOT EXISTS `newtable` (
  `name` int(11) NOT NULL AUTO_INCREMENT,
  `id` int(11) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `newtable` (`name`, `id`) VALUES
(1, 2);

and a second file with,

CREATE TABLE IF NOT EXISTS `newtable` (
  `name` int(11) NOT NULL AUTO_INCREMENT,
  `id` int(11) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `newtable` (`name`, `id`) VALUES
(1, 2),
(4, 5);

Then I would get,

INSERT INTO `newtable` (`name`, `id`) VALUES
(1, 2),
(4, 5);

So how is this possible???

Upvotes: 0

Views: 884

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Here is one method, assuming that neither table has duplicates:

select (case when max(src) = 1 then 'DELETED'
              else 'ADDED'
         end) as what, name, id
from ((select 1 as src, name, id
       from newtable1 t
      ) union all
      (select 2 as src, name, id
       from newtable2 t
      )
     ) t
group by name, id
having count(*) = 1

This returns whether the row is added or deleted from one table to the other. You can insert these results into another table -- or wherever -- using an insert statement.

Upvotes: 1

Related Questions