INSERT with SELECT

问题:

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?

回答1:

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.

回答2:

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 ...

回答3:

Correct Syntax: select spelling was wrong

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

回答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

回答5:

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...

回答6:

The right Syntax for your query is:

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