Insert into a table conditionally using values from another table

Question

I have a good idea of the pseudo-logic of what I want to do - just struggling to think of the syntax to put it into practice.

I’ve got three tables: product_images product product_import

At a high level - I want to insert a row into the product_images table with just two values (image_url, product_id) - the image url can be found in the product_import table along with an product_id. This product_id is the old ID of the product (migrating from another system) - this is recorded as old_id in the product table. Therefore the retrieval of the image_url works conditionally on the basis that: the product_id in the product_import table has a match with the old_id value in the product table. If it does match - then insert the value of the matching image_url from the product_import table and the new product_id that matches from the product table (if the old_id is found)

My guess at the SQL statement is something along the lines of:

INSERT INTO product_image(image_url, product_id)
SELECT product_import.image_url, product.id WHERE product.old_id = product_import.id;

Answer 1

This was what I needed in the end..

INSERT INTO product_images(image_url, product_id, account_id, is_thumbnail, created_at, modified_at)
SELECT `value`, id, account_id, "1", now(), now()
FROM product_import
JOIN product ON product_import.entity_id = product.old_id
WHERE product.account_id=1;