bluewater_sailor bluewater_sailor - 1 year ago 80
MySQL Question

What is an efficient way to get a diff of two MySQL tables based on several columns?

I'm trying to reconcile the data that I already have (an inventory list) with a daily update, which is a table with the same layout; all I need are the rows that have differences in any of the four fields from the current table (differences in other fields aren't considered.)

What I'm doing now is this:

insert into diff
select * from new n
where concat(n.distrib_pn, n.available_total, n.cost, not in
(select concat(c.distrib_pn, c.available_total, c.cost, from current c);

It's very slow: ~35 seconds for two files of ~7,000 rows.
, and
tables are indexed on
. I've also tried doing this without using
, but couldn't figure out the syntax.

Answer Source

You can use join query to perform this action.

insert into diff
    select n.* from new n inner join current c on 
          n.distrib_pn != c.distrib_pn and
          n.available_total != c.available_total and
          n.cost != c.cost and

Hope this will be helpful

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download