INSERT with SELECT

Question

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 1

I think your INSERT statement is wrong, see correct syntax: http://dev.mysql.com/doc/refman/5.1/en/insert.html

edit: as Andrew already pointed out...

Answer 2

The right Syntax for your query is:

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

Answer 3

Correct Syntax: select spelling was wrong

INSERT INTO courses (name, location, gid)
SELECT name, location, 'whatever you want' 
FROM courses 
WHERE cid = $ci 

Answer 4

Sure, what do you want to use for the gid? a static value, PHP var, ...

A static value of 1234 could be like:

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

Answer 5

Yes, it is. You can write :

INSERT INTO courses (name, location, gid) 
SELECT name, location, 'whatever you want' 
FROM courses 
WHERE cid = $ci

or you can get values from another join of the select ...

Answer 6

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.