Geek Answers Handbook

MYSQL: the difference between tables

I have two tables:

  • Products

  • products_tmp

The table has products_tmpfewer fields. But all the fields in products_tmpare in the products.

When importing data, I fill out the table products_tmp, after which I need to do 3 things:

  • check new products in products_tmpwhich are not included in the products (get identifiers) (DONE)

  • check old products in products that are not in products_tmp(get identifiers for subsequent removal) (DONE)

  • Check the differences from the rest. Both tables have a unique hash product identifier.

And I need to check the differences in the fields of title, text, price, photosand category_id. And getting tmp ids, so I can update the product table with new values.

Is it possible to use diff only in mysql?

+5
source share
1 answer
  • check new products in products_tmp that are not included in the products (get identifiers) (DONE)

This is straightforward ...

SELECT pt.*
FROM products_tmp pt
LEFT JOIN products p ON p.product_id = pt.product_id
WHERE p.product_id IS NULL;
  • check old products in products that are not in product_tmp (get identifiers for subsequent removal) (DONE)

A always prefers to replace tables with execution RIGHT JOINS- personal preferences ...

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 the differences from the rest. Both tables have a unique hash product identifier.

. , , ...

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;

UNION , , 2 , . , , , .

+5

More articles:


All Articles