bluewater_sailor bluewater_sailor - 2 months ago 11
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, n.map) not in
(select concat(c.distrib_pn, c.available_total, c.cost, c.map) from current c);


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

Answer

Hi
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
          n.map != c.map;

Hope this will be helpful

Comments