INSERT with SELECT

Question

I have a query that inserts using a SELECT statement:

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?

Nerveless

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

Offprint

Worked, thank you very much. With Insert Select, the parenthesis aren't required around field names. But when you start specifying values on top of the select, apparently you need () around the field names. – Nerveless

Note that you cannot use aliases in the insert clause : "INSERT INTO courses t1 (t1.name, t1.location, t1.gid) [...] will fail. – Imogene

@Offprint is there any method to return this same select without running select next time? if I want to insert and get the result of courses table – Cenac

@TAHASULTANTEMURI Different question. But if I understand what you're asking, SQL Server (not what was asked, but what I use these days) has the OUTPUT clause, which lets you put whatever you inserted into another table also. I don't think MySQL has an equivalent. You might create a temporary table, select into that table, then populate courses from that table, then use the temp table for whatever else you needed. – Offprint

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

Terrijo

INSERT INTO courses name, location, gid SELECT name, location, '$this->gid' FROM courses WHERE cid = $cid -- I get the error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'name, location, gid SELECT name, location, '22' FROM courses WHERE cid = 23' at line 1 – Nerveless

I guess your gid field is an integer so: '22' must be 22. Change '$this->gid' by $this->gid – Bookstand

22 and '22' should not matter to sql, but I put a string in case that the field is similar to an unique identifier. – Terrijo

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 

Miasma

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

Franky

Answer 5

Of course you can.

One thing should be noted however: The INSERT INTO SELECT statement copies data from one table and inserts it into another table AND requires that data types in source and target tables match. If data types from given table columns does not match (i.e. trying to insert VARCHAR into INT, or TINYINT intoINT) the MySQL server will throw an SQL Error (1366).

So be careful.

Here is the syntax of the command:

INSERT INTO table2 (column1, column2, column3)
SELECT column1, column2, column3 FROM table1
WHERE condition;

Side note: There is a way to circumvent different column types insertion problem by using casting in your SELECT, for example:

SELECT CAST('qwerty' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin;

This conversion (CAST() is synonym of CONVERT() ) is very useful if your tables have different character sets on the same table column (which can potentially lead to data loss if not handled properly).

Russel

Answer 6

We all know this works.

INSERT INTO `TableName`(`col-1`,`col-2`)
SELECT  `col-1`,`col-2` 

===========================
Below method can be used in case of multiple "select" statements. Just for information.

INSERT INTO `TableName`(`col-1`,`col-2`)
 select 1,2  union all
 select 1,2   union all
 select 1,2 ;

Niki

Answer 7

The right Syntax for your query is:

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

Vincevincelette