w3resource

MySQL INSERT rows with GROUP BY

INSERT rows with GROUP BY

In this page, we have discussed how to insert values into a table using MySQL INSERT INTO statement, when the column names and values are collected from another identical table using MySQL SELECT and GROUP BY.

This way you can insert the rows of one table into another identical table for a specific group.

Example

Sample table: purchase


Code:

INSERT INTO testpurchase 
SELECT * 
FROM purchase 
GROUP BY cate_id;

Relational Algebra Expression:

Relational Algebra Expression: MySQL Joins: INSERT rows with GROUP BY.

Relational Algebra Tree:

Relational Algebra Tree: MySQL Joins: INSERT rows with GROUP BY.

Explanation:

The above statement has performed the following -

1. the rows of 'purchase' table have grouped according to the 'cate_id',
2. and inserted into the table 'testpurchase'.

To see some specific columns from inserted rows here is the code below -

SELECT  invoice_no,ord_no,book_name 
FROM testpurchase;

Output:

mysql> SELECT  invoice_no,ord_no,book_name 
    -> FROM testpurchase;
+------------+----------------+---------------------------------+
| invoice_no | ord_no         | book_name                       |
+------------+----------------+---------------------------------+
| INV0001    | ORD/08-09/0001 | Introduction to Electrodynamics | 
| INV0002    | ORD/08-09/0002 | Transfer  of Heat and Mass      | 
| INV0006    | ORD/07-08/0007 | Guide to Networking             | 
+------------+----------------+---------------------------------+
3 rows in set (0.00 sec)

MySQL INSERT records with ORDER BY

INSERT records with ORDER BY

Here in the following, we have discussed how to insert values into a table using MySQL INSERT INTO statement when the column names and values are collected from another identical table using MySQL SELECT, GROUP BY, and ORDER BY.

This way you can insert the rows of one table into another identical table when columns are sorted by a specific column.

Example

Sample table: purchase


Code:

INSERT INTO testpurchase 
SELECT * FROM purchase 
GROUP BY cate_id 
ORDER BY cate_id;

Relational Algebra Expression:

Relational Algebra Expression: MySQL Joins: MySQL INSERT records with ORDER BY.

Relational Algebra Tree:

Relational Algebra Tree: MySQL Joins: MySQL INSERT records with ORDER BY.

Explanation:

The above statement has performed the following -

1. the rows of 'purchase' table have grouped according to the 'cate_id',
2. the group of 'purchase' table has arranged in ascending order
3. and inserted into the table 'testpurchase'.

To see some specific columns from inserted rows here is the code below -

SELECT  cate_id,ord_no,book_name FROM testpurchase;

Output:

mysql> SELECT  cate_id,ord_no,book_name FROM testpurchase;
+---------+----------------+---------------------------------+
| cate_id | ord_no         | book_name                       |
+---------+----------------+---------------------------------+
| CA001   | ORD/08-09/0001 | Introduction to Electrodynamics | 
| CA002   | ORD/08-09/0002 | Transfer  of Heat and Mass      | 
| CA003   | ORD/07-08/0007 | Guide to Networking             | 
+---------+----------------+---------------------------------+
3 rows in set (0.00 sec)

Previous: MySQL INSERT
Next: INSERT with LEFT JOIN



Follow us on Facebook and Twitter for latest update.