Mastering MySQL 4 [Electronic resources]

Adding Records to a Table from Other Tables

with INSERT SELECT

The INSERT statement also allows you to add records, or parts of records, that exist in other tables. For example, let's say you want to create a new table containing the customer names and the values of all the purchases they have made. The query to return the results you want would be the following:

mysql> SELECT first_name,surname,SUM(value) FROM sales NATURAL JOIN
 customer GROUP BY first_name, surname;
+------------+-------------+------------+
| first_name | surname     | SUM(value) |
+------------+-------------+------------+
| Johnny     | Chaka-Chaka |        500 |
| Patricia   | Mankunku    |        450 |
| Winston    | Powers      |        750 |
| Yvonne     | Clegg       |       5800 |
+------------+-------------+------------+

First you'll need to create the table to receive the results:

mysql> CREATE TABLE customer_sales_values(first_name
 VARCHAR(30), surname VARCHAR(40), value INT);

Now, you insert the results into this table:

mysql> INSERT INTO customer_sales_values(first_name,surname,value)
 SELECT first_name,surname, SUM(value) FROM sales NATURAL JOIN
 customer GROUP BY first_name, surname;

The customer_sales_values table now contains the following:

mysql> SELECT * FROM customer_sales_values;
+------------+-------------+-------+
| first_name | surname     | value |
+------------+-------------+-------+
| Johnny     | Chaka-Chaka |   500 |
| Patricia   | Mankunku    |   450 |
| Winston    | Powers      |   750 |
| Yvonne     | Clegg       |  5800 |
+------------+-------------+-------+