How to Create a Table from Another Table in MySQL

08/06/2021

Contents

In this article, you will learn how to create a table from another table in MySQL.

Creating a table from another table in MySQL

In MySQL, you can create a new table based on an existing table using the CREATE TABLE AS SELECT statement. This statement allows you to copy the structure and data from one table to another.

Syntax

Here’s the basic syntax for creating a table from another table:

CREATE TABLE new_table_name AS SELECT * FROM existing_table_name;

This will create a new table with the same structure and data as the existing table.

You can also select specific columns from the existing table to include in the new table, and you can apply filters and transformations to the data using the SELECT statement.

Examples

Copying an entire table

CREATE TABLE new_table AS SELECT * FROM existing_table;

This statement will create a new table called new_table with the same structure and data as the existing_table.

Copying specific columns from a table

CREATE TABLE new_table AS SELECT column1, column2 FROM existing_table;

This statement will create a new table called new_table with only the columns column1 and column2 from the existing_table.

Applying a filter to the data

CREATE TABLE new_table AS SELECT * FROM existing_table WHERE column1 = 'value';

This statement will create a new table called new_table with only the rows from the existing_table where column1 has the value ‘value’.

Applying a transformation to the data

CREATE TABLE new_table AS SELECT column1, column2 * 2 AS column3 FROM existing_table;

This statement will create a new table called new_table with the columns column1 and column3. The column3 will contain the values of column2 from existing_table multiplied by 2.

#MySQL