Transfer data from memory table to production one by criteria-CodePudding

I wander around can I transfer the data gathered in memory table to actual one by sql query only.

The two tables have the same structure and pk is products_id(int, AI)

The problem is the criteria is completely different than the pk. The products are identified by 2 columns - barcode and company.

So ignoring the pk in whole, I need to update the data if in actual table there is a row with the same barcode and company, and insert new record if there is none.

Tried this:

INSERT INTO products (products_sku, ...)
SELECT products_sku... FROM temp_products
WHERE (temp_products.products_barcode = products.products_barcode) AND (temp_products.products_comp = products.products_comp)

But i dont have access in the select to products table so to make the filtering

CodePudding user response：

I think you need to add a unique key on products_barcode and products_comp:

ALTER TABLE products ADD UNIQUE KEY (products_barcode, products_comp);

Once you have it you can perform insert-or-update in one statement:

INSERT INTO products (/* all columns except the id */)
SELECT /* all columns except the id */
FROM products_sku
ON DUPLICATE KEY UPDATE some_field = VALUES(some_field), ... 
/* list all columns except the id / barcode / comp */;

So when it meets a duplicate barcode/comp pair it will fall into the ON DUPLICATE KEY UPDATE and won't insert. Read more how it works: https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html