Transfer data from memory table to production one by criteria

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

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating