PHP —

INSERTing with a SELECT Statement

We'll now show how insertion and querying can be closely tied together with an INSERT INTO ... SELECT statement. This is useful for copying data and, if needed, modifying the data as it is copied.

Consider an example to create a permanent record of the total sales to each customer up to this month, let's say it's April. First, create a simple table to store the customer and sales details:

CREATE TABLE salesuntilapril
(
 cust_id int(5) NOT NULL,
 surname varchar(50),
 firstname varchar(50),
 totalsales float(5,2),
 PRIMARY KEY (cust_id)
);

Now issue a combined INSERT INTO ... SELECT statement to populate the new table with the customer details and the total sales:

INSERT INTO salesuntilapril
  (cust_id, surname, firstname, totalsales)
   SELECT customer.cust_id, surname, firstname, SUM(price)
     FROM customer, items
     WHERE customer.cust_id = items.cust_id
     GROUP BY items.cust_id;

The four attributes listed in the SELECT statement are mapped to the four attributes listed in the INSERT INTO statement. For example, the customer.cust_id in the SELECT line is mapped into cust_id in the salesuntilapril table.

A query on the new table shows part of the results:

SELECT * from salesuntilapril;
+---------+-------------+-----------+------------+
| cust_id | surname     | firstname | totalsales |
+---------+-------------+-----------+------------+
|       2 | LaTrobe     | Anthony   |     566.42 |
|       3 | Fong        | Nicholas  |     821.78 |
|       4 | Stribling   | James     |     181.69 |
|       5 | Choo        | Richard   |     534.99 |
|       6 | Eggelston   | Perry     |     657.37 |
|       7 | Mellaseca   | Kym       |    1216.88 |

There are two sensible limitations of the INSERT INTO ... SELECT statement: first, the query can't contain an ORDER BY, and second, the FROM clause can't contain the target table of the INSERT INTO.