12.18 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');
|
