Usage of insert into select

In relational databases, INSERT INTO SELECT is a common SQL statement used to insert the results of a query into another table. This syntax structure is very useful to easily copy data from one table to another table, or to filter and insert data based on some conditions.

The syntax structure of INSERT INTO SELECT is as follows:

INSERT INTO 表名 (列1, 列2, 列3, ...)
SELECT 列1, 列2, 列3, ...
FROM 来源表
WHERE 条件;

Among them, the INSERT INTO clause specifies the target table and target column into which data is to be inserted. The SELECT clause defines which table to select data from and specifies which columns to insert into the target table. You can select the appropriate columns as needed so that all columns do not have to be inserted into the target table.

The source table in the SELECT clause refers to the table from which data is to be selected. It can be a specific table name or a query result set. When using INSERT INTO SELECT, you can nest multiple query statements as needed to meet data transfer and filtering requirements.

The WHERE clause is optional and is used to filter data that meets specific conditions in the source table. For example, you can use the WHERE clause to limit the insertion of only rows that meet specific conditions, or use other operators (such as IN, LIKE, etc.) to further filter the data.

The following are some example usages of INSERT INTO SELECT:

Simple insert operation:

Suppose there are two tables A and B. Table A contains columns id, name and age, table B contains columns id and address. To insert data in table A into the corresponding column in table B, you can use the following statement:

INSERT INTO B (id, address)
SELECT id, name
FROM A;

In this way, the value of the id column in table A will be inserted into the id column of table B. Table The value of the name column in A will be inserted into the address column of table B. Other columns will be ignored.

Use WHERE clause to filter:

If you only want to insert data that meets specific conditions, you can add a WHERE clause in the SELECT statement. For example, to only insert records whose age is greater than 18 in table A into table B, you can use the following statement:

INSERT INTO B (id, address)
SELECT id, name
FROM A
WHERE age > 18;

In this way, only rows that meet the conditions will be inserted into table B.

Insert using subqueries:

In the SELECT clause, you can also use subqueries to select data. For example, to insert rows that meet the conditions into another table, you can use the following statement:

INSERT INTO C (id, address)
SELECT id, address
FROM B
WHERE id IN (SELECT id FROM A WHERE age > 18);

In this way, first select the ids that meet the conditions from table A, and then select the corresponding ids from table B based on these ids. records and insert them into table C.

Summary

INSERT INTO SELECT is a powerful SQL statement that can easily copy data from one table to another table, or based on some conditions Filter and insert data. Its flexibility makes it very useful in real-world database operations.

The above is the detailed content of Usage of insert into select. For more information, please follow other related articles on the PHP Chinese website!