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