MYSQL: diff between tables

Question

I have two tables:

• products

• products_tmp

The products_tmp table has less fields. But all the fields that are in products_tmp are in products.

On import of data I populate the products_tmp table and after that I need to do 3 things:

1. check the new products in products_tmp that are not in products (get ids) (DONE)

2. check the old products in products that are not in products_tmp (get ids for later deletion) (DONE)

3. check for differences on the rest. Both tables have a product-hash unique identifier.

And I need to check differences in fields title, text, price, photos and category_id. And getting the tmp ids, so I can then update the products table with new values.

Is it possible to do the diff only in mysql?

Answer 1

1. check the new products in products_tmp that are not in products (get ids) (DONE)

This is straight forward...

SELECT pt.*
FROM products_tmp pt
LEFT JOIN products p ON p.product_id = pt.product_id
WHERE p.product_id IS NULL;

1. check the old products in products that are not in products_tmp (get ids for later deletion) (DONE)

A always prefer swapping tables to performing RIGHT JOINS - personal preference...

SELECT p.*
FROM products p
LEFT JOIN products_tmp pt ON p.product_id = pt.product_id
WHERE pt.product_id IS NULL;

3.check for differences on the rest. Both tables have a product-hash unique identifier.

This one is a little more difficult. Assuming you don't want to do a field-by-field comparison, you could do this...

SELECT product_id, count(*) cnt
FROM (
    SELECT p.product_id, p.field2, p.field3
    FROM products p
    UNION
    SELECT pt.product_id, pt.field2, pt.field3
    FROM products_tmp pt
) pd
GROUP BY product_id
HAVING cnt > 1;

The UNION should naturally join the duplicates together into a single row, so anything with a count of 2 based on your primary key should be a duplicate row. If you put the results of this into a temporary table, you can do more queries to see what the differences are.