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  quot name  location quot  for the insert  and set gid to something else in the query

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

The right Syntax for your query is   INSERT INTO courses  name  location  gid   SELECT  name  location  gid   FROM courses  WHERE cid    cid

User · 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

User · Answer

Correct Syntax  select spelling was wrong  INSERT INTO courses  name  location  gid  SELECT name  location   whatever you want   FROM courses  WHERE cid    ci

User · Answer

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  quot select quot  statements  Just for information  INSERT INTO  TableName   col-1   col-2    select 1 2  union all  select 1 2   union all  select 1 2

[mysql] INSERT with SELECT

Examples related to mysql

Examples related to sql

Examples related to insert-select