G_V G_V - 3 months ago 8
SQL Question

MySQL select from one table, insert into another

I created two MySQL tables. One to import a CSV in, called

staging_part_type
and one where the actual
part_type
records are supposed to end up called
part_type
, minus any records that have a
product_code
already present. Both tables are identical, consisting of the columns
id, in_use, default_name, product_code
.

Is there a specific name for doing something like this? I haven't found any complete tutorials on this, only a bunch of separate ones and I'm failing to put them together in a working query as I lack experience in this subject and after a week of reading, I still barely understand what I'm doing.

Something like this?

INSERT IGNORE INTO part_type (in_use, default_name, product_code)
SELECT (in_use, default_name, product_code)
FROM staging_part_type
WHERE part_type.product_code IS NOT staging_part_type.product_code;


Code to create the table, both tables are identical except for the name.

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 ;


Can someone please explain to me how to do this or point me to a resource on this subject?

Answer

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.