Inserting Records in One Table That Include Values from Another
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.
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.
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 ...