Hey all, I'm looking for a way to insert values from one table from another in a pretty straightforward way, I just can't get the syntax...
If table 1 has fields A,B, and C, and table 2 has 20 fields... I want to insert into:
A a value that I choose
B a value selected from one field from table 2 via a select statement
C another value that I choose
I am not sure what you mean by "A value that I choose" but there are several ways. Here is an example from the mysql cookbook that should do the trick:
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:
INSERT INTO painting (a_id, title, state, price)
SELECT a_id, 'Les jongleurs', 'MN', 51
FROM artist WHERE name = 'Renoir';