I’m joining 3 tables:
SELECT catalog_product_entity.entity_id AS product_id, catalog_product_entity.sku, customer_entity_varchar.entity_id AS customer_id, customer_entity_varchar.value, customer_entity_varchar.attribute_id, import_tmp.customer, import_tmp.grp, import_tmp.qty, import_tmp.price FROM catalog_product_entity INNER JOIN import_tmp ON catalog_product_entity.sku = import_tmp.sku INNER JOIN customer_entity_varchar ON import_tmp.customer = customer_entity_varchar.value OR import_tmp.grp = customer_entity_varchar.value WHERE customer_entity_varchar.attribute_id = 139 OR customer_entity_varchar.attribute_id = 140
Currently, I’m fetching the data, build a new array and insert it into my final table.
INSERT INTO final_table(`c_id`, `p_id`, `price`) VALUES (customer_id, product_id, price);
However, I wonder if it’s possible to do this in one go, so basically directly from the temporary table?
Advertisement
Answer
Yes. Use INSERT . . . SELECT
:
INSERT INTO final_table (c_id, p_i`, price) SELECT cev.entity_id AS customer_id, cpe.entity_id AS product_id, it.price FROM catalog_product_entity cpe JOIN import_tmp it ON cpe.sku = it.sku JOIN customer_entity_varchar cev ON cev.value IN (it.customer, it.grp) WHERE cev.attribute_id IN (139, 140);
Note that I introduced table aliases. These make the query easier to write and to read. And IN
is easier to follow than repeated OR
conditions.