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 Tree:
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 Tree:
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics