How Can I Use INSERT with SELECT to Insert Specific Columns and Set Custom Values?

Understanding “INSERT WITH SELECT” query

The "INSERT WITH SELECT" query combines an INSERT statement with a SELECT statement to insert multiple rows into a database table based on the data retrieved from the SELECT statement.

Flexibility of “INSERT WITH SELECT” query

While the INSERT statement typically allows you to specify a value for each column in the target table, the "INSERT WITH SELECT" query provides greater flexibility. In these queries, you can selectively include or exclude columns in the SELECT statement, allowing you to insert data into specific columns or modify values ​​during the insertion process.

Example: Insert only selected columns and set custom values ​​for columns

Consider the query provided in the question:

<code class="language-sql">INSERT INTO courses (name, location, gid) 
SELECT name, location, gid 
FROM courses 
WHERE cid = $cid</code>

In this query, the SELECT statement retrieves the name, location, and gid columns from the courses table, but only intends to insert name and location into the target courses table. To achieve this, you can modify the query as follows:

<code class="language-sql">INSERT INTO courses (name, location, gid)
SELECT name, location, 1
FROM courses
WHERE cid = 2</code>

In the modified query, the SELECT statement remains the same, but we specify a constant value of 1 for the gid column in the INSERT statement. This allows us to insert the name and location into courses while setting the gid to a fixed value of 1. Note that you can replace 1 with any constant of the same type as gid.

The above is the detailed content of How Can I Use INSERT with SELECT to Insert Specific Columns and Set Custom Values?. For more information, please follow other related articles on the PHP Chinese website!