MySQL 8 Cookbook

Saving as a table

You can save the results of a SELECT statement into a table. Even if the table does not exist, you can use CREATE and SELECT to create the table and load the data. If the table already exists, you can use INSERT and SELECT to load the data.

You can save the titles into a new titles_only table:

mysql> CREATE TABLE titles_only AS SELECT DISTINCT title FROM titles;Query OK, 7 rows affected (0.50 sec)Records: 7  Duplicates: 0  Warnings: 0

If the table already exists, you can use the INSERT INTO SELECT statement:

mysql> INSERT INTO titles_only SELECT DISTINCT title FROM titles;Query OK, 7 rows affected (0.46 sec)Records: 7  Duplicates: 0  Warnings: 0

To avoid duplicates, you can use INSERT IGNORE. However, in this case, there is ...