Insert into - help needed

[DaPrince]

I am trying to insert all rows from one table that are not present in another table.

I have the select statement working here. But I cannot figure out how to do the insert statement.

select kt_vm_product.product_id from kt_vm_product
LEFT JOIN kt_vm_product_category_xref3 ON kt_vm_product.product_id=kt_vm_product_category_xref3.product_id
where kt_vm_product_category_xref3.product_id is NULL;

I want this output to be put into the table vm_product_category_xref3 (plus two more fields that are static)

[johanafm]

Have you tried using INSERT INTO tableName (fields) SELECT equal, number, of, fields FROM ...

[DaPrince]

Well, I actually tried that, but with a little change I found another way - not a very fine way, but it works.

INSERT INTO kt_vm_product_category_xref (product_id) 

SELECT kt_vm_product.product_id 
FROM kt_vm_product
LEFT 
JOIN kt_vm_product_category_xref
ON kt_vm_product.product_id=kt_vm_product_category_xref.product_id 
WHERE kt_vm_product_category_xref.product_id is NULL;

update kt_vm_product_category_xref set category_id = "3", product_list ="1"
WHERE category_id = "0";

[tomhath]

LEFT JOIN ... WHERE NULL is roughly equivalent to a NOT EXISTS, but I find the subselect easier to read:

http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html