Inserting Records in One Table That Include Values from Another

12.18.1 Problem

You need to insert a record into a table that requires an ID value. But you know only the name associated with the ID, not the ID itself.

12.18.2 Solution

Assuming that you have a lookup table that associates names and IDs, create the record using INSERT INTO ... SELECT, where the SELECT performs a name lookup to obtain the corresponding ID value.

12.18.3 Discussion

We've used lookup tables often in this chapter in join queries, typically to map ID values or codes onto more descriptive names or labels. But lookup tables are useful for more than just SELECT statements. They can help you create new records as well. To illustrate, we'll use the artist and painting tables containing information about your art collection. Suppose you travel to Minnesota, where you find a bargain on a $51 reproduction of "Les jongleurs" by Renoir. Renoir is already listed in the artist table, so no new record is needed there. But you do need a record in the painting table. To create it, you need to store the artist ID, the title, the state where you bought it, and the price. You already know all of those except the artist ID, but it's tedious to look up the ID from the artist table yourself. Because Renoir is already listed there, why not let MySQL look up the ID for you? To do this, use INSERT ... SELECT to add the new record. Specify all the literal values that you know in the SELECT output column list, and use a WHERE clause to look up the artist ID from the name:

mysql> INSERT INTO painting (a_id, title, state, price)
 -> SELECT a_id, 'Les jongleurs', 'MN', 51
 -> FROM artist WHERE name = 'Renoir';

Naturally, you wouldn't want to write out the full text of such a query by hand each time you get a new painting. But it would be easy to write a short script that, given the artist name, painting title, origin, and price, would generate and issue the query for you. You could also write the code to make sure that if the artist is not already listed in the artist table, you generate a new ID value for the artist first. Just issue a statement like this prior to creating the new painting record:

INSERT IGNORE INTO artist (name) VALUES('artist name');