INSERT with SELECT

mysql sql insert-select

Kyle · Mar 22, 2011 · Viewed 399.3k times · Source

I have a query that inserts using a select:

INSERT INTO courses (name, location, gid) 
            SELECT name, location, gid 
              FROM courses 
             WHERE cid = $cid

Is it possible to only select "name,location" for the insert, and set gid to something else in the query?

Answer

Yes, absolutely, but check your syntax.

INSERT INTO courses (name, location, gid)
SELECT name, location, 1
FROM   courses
WHERE  cid = 2

You can put a constant of the same type as gid in its place, not just 1, of course. And, I just made up the cid value.