I created two MySQL tables. One to import a CSV in, called
id, in_use, default_name, product_code
INSERT IGNORE INTO part_type (in_use, default_name, product_code)
SELECT (in_use, default_name, product_code)
WHERE part_type.product_code IS NOT staging_part_type.product_code;
CREATE TABLE IF NOT EXISTS `part_type` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`in_use` tinyint(1) NOT NULL,
`default_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`product_code` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=580 ;
As is often the case in SQL, there are two ways to do this, with a Subquery and with a join.
The subquery method is easier to understand and write, but the join method is more efficient and what you'd do if this was a mission critical query.
Since you're just importing a simple CSV, we will use a "correlated subquery" with "exists", which works like this:
INSERT INTO part_type (in_use, default_name, product_code) SELECT in_use, default_name, product_code FROM staging_part_type WHERE NOT EXISTS (SELECT product_code FROM part_type WHERE product_code = staging_part_type.product_code)
You were thinking along the right lines, but you need to set up two queries and link them together. The query in parentheses runs and the only results that come back are ones that don't already exist in the row. Try running the second line onwards by itself first, to see what you would get back, and then add in the first line to make the insert happen.