Can I INSERT into a table with SELECT, assigning a different value to one column?

Combining INSERT and SELECT with Modified Column Values

This example demonstrates how to insert data into the courses table using a SELECT statement, while simultaneously assigning a specific value to the gid column, regardless of the value retrieved from the source table.

The original query attempts to copy data from the courses table:

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

The question is whether we can modify the gid value during the insertion process.

Solution

Yes, this is achievable. The key is to replace the gid column in the SELECT statement with a constant value or a different expression. The following query illustrates this:

<code class="language-sql">INSERT INTO courses (name, location, gid)
SELECT name, location, 1  -- '1' is the new gid value
FROM   courses
WHERE  cid = 2  -- Example condition; replace '2' with your desired cid</code>

In this modified query, the gid column in the INSERT statement will always receive the value 1, regardless of the gid value in the selected row from the courses table. You can substitute 1 with any appropriate constant or expression that results in a value compatible with the gid column's data type. The WHERE clause filters the source data, ensuring only the relevant row is used for insertion.

The above is the detailed content of Can I INSERT into a table with SELECT, assigning a different value to one column?. For more information, please follow other related articles on the PHP Chinese website!