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.
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
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.
I think you need to add a unique key on
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