0

Following is my query in which I am making insertion in one table and some specific values from another table. Kindly let me know what is an appropriate way to write such a query. Thank,

Insert into c_table (name,number,class) 
values ('1','2',select count(id) from thetable)
Taryn
  • 242,637
  • 56
  • 362
  • 405
user3027531
  • 282
  • 1
  • 5
  • 20
  • 3
    Are there any examples out there of using SELECT in conjunction with INSERT, or are we breaking new groud here? – Strawberry Feb 05 '14 at 20:52

2 Answers2

3

Instead of a VALUES() clause, use the INSERT INTO ...SELECT pattern with static values for the first two columns in the SELECT component. The values of the first two pseudo-columns in the SELECT list will always be the same, but the value for the COUNT() will change dynamically with thetable. Be sure to add a WHERE clause to the SELECT statement if necessary.

INSERT INTO c_table (name, number, class)
  SELECT
    '1' AS name,
    '2' AS number,
    COUNT(id) AS class
  FROM thetable
 /* WHERE <conditions if necessary> */
Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
1

Very close, but try enclosing your subquery in parentheses:

insert into c_table (name,number,class) 
values ('1','2',(select count(id) from thetable))
Taryn
  • 242,637
  • 56
  • 362
  • 405
AS7K
  • 417
  • 4
  • 20
  • Very Close to be CLOSED DOWN you mean – z atef Feb 05 '14 at 20:54
  • 1
    @NullSoulException The query he mentioned actually worked. What's the issue with this query? – user3027531 Feb 05 '14 at 20:57
  • 1
    @user3027531 Doesn't seem like the right form to recommend, since as soon as you change that subquery such that it returns more than one row, what will MySQL say? I know it likes cowboy-style group by, but I'm not sure if it will like cowboy-style subqueries. – Aaron Bertrand Feb 05 '14 at 21:06
  • 2
    @AaronBertrand I never heard of "cowboy-style `GROUP BY`" but that has to be the perfect term to describe MySQL's lenience in `GROUP BY`. – Michael Berkowski Feb 05 '14 at 21:31