Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from MySQL Cookbook

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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata