120

As the title says, I am trying to insert into one table selecting values from another table and some default values. 

INSERT INTO def (catid, title, page, publish) 
(SELECT catid, title from abc),'page','yes')


INSERT INTO def (catid, title, page, publish) 
VALUES
((SELECT catid, title from abc),'page','yes'))

The first query gives a mysql error and the second one gives column count does not match.

What do I need to do?

SQB
  • 3,926
  • 2
  • 28
  • 49
wannabeprogrammer
  • 1,215
  • 2
  • 9
  • 8

5 Answers5

261

You simply have to do:

INSERT INTO def (catid, title, page, publish) 
SELECT catid, title, 'page','yes' from `abc`
Nican
  • 7,825
  • 3
  • 27
  • 26
  • 4
    One important note: you have to put the names of the column from the table where you want to write on the first line, and the column names from the table you're reading go on the second ligne. So in this answer, `catid` and `title` don't point to the same table. – A.L Feb 04 '15 at 17:33
  • 4
    Another important note: The different types of quotes do different things. \`field\` is a field name while 'value' is a value. – zeusstl Jan 20 '16 at 20:11
  • 3
    Notice that the keyword VALUES is not used – kikerrobles Nov 19 '18 at 18:10
  • INSERT INTO offer_masti.`city` (NULL, '1', '1', citydb.city_name, NULL, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) select test.cities.city as city_name from test.cities as citydb; i am using this query it give me error can any one help me to solve this error? – Chintan Mathukiya Apr 07 '20 at 12:57
12

If you want to insert all the columns then 

insert into def select * from abc;

here the number of columns in def should be equal to abc.

if you want to insert the subsets of columns then 

insert into def (col1,col2, col3 ) select scol1,scol2,scol3 from abc;

if you want to insert some hardcorded values then 

insert into def (col1, col2,col3) select 'hardcoded value',scol2, scol3 from abc;
kanishka vatsa
  • 2,074
  • 18
  • 8
12
INSERT INTO def (field_1, field_2, field3) 
VALUES 
('$field_1', (SELECT id_user from user_table where name = 'jhon'), '$field3')
Frankie
  • 490
  • 8
  • 23
  • 1
    While this code may answer the question, providing additional [context](https://meta.stackexchange.com/q/114762) regarding _how_ and/or _why_ it solves the problem would improve the answer's long-term value. It also doesn't hurt to mention why this answer is more appropriate than others. – Dev-iL Mar 20 '17 at 21:34
8

If you you want to copy a sub-set of the source table you can do:

INSERT INTO def (field_1, field_2, field3)

SELECT other_field_1, other_field_2, other_field_3 from `abc`

or to copy ALL fields from the source table to destination table you can do more simply:

INSERT INTO def 
SELECT * from `abc`
Hartley Brody
  • 8,669
  • 14
  • 37
  • 48
Redips77
  • 121
  • 2
  • 4
3

With MySQL if you are inserting into a table that has a auto increment primary key and you want to use a built-in MySQL function such as NOW() then you can do something like this:

INSERT INTO course_payment 
SELECT NULL, order_id, payment_gateway, total_amt, charge_amt, refund_amt, NOW()
FROM orders ORDER BY order_id DESC LIMIT 10;
AdrieanKhisbe
  • 3,899
  • 8
  • 37
  • 45
crmpicco
  • 16,605
  • 26
  • 134
  • 210