CODEWITHSUNDEEP
mysqlsql
woj_jas
woj_jas

Reputation: 1088

Inserting values from 1 table to another error

I'm Having 2 tables

modules_templates

and

templates

In table templates i have 75 records . I want to insert into table modules_templates some data which template_id in modules_templates = template_id from templates. I created this query :

INSERT INTO `modules_templates` (`module_template_id`,`module_template_modified`,`module_id`,`template_id`) VALUES  ('','2014-04-14 10:07:03','300',(SELECT template_id FROM templates WHERE 1))

And I'm having error that #1242 - Subquery returns more than 1 row , how to add all 75 rows in 1 query ?

Upvotes: 0

Views: 38

Answers (4)

user2009750
user2009750

Reputation: 3187

Try this

INSERT 
    INTO `modules_templates` 
    (`module_template_id`,`module_template_modified`,`module_id`,`template_id`) 

    (SELECT '','2014-04-14 10:07:03','300',template_id FROM templates WHERE 1)

Your query didn't work because you were inserting value for one row, where last field i.e result of sub query was multirow, so what you had to do was to put those single row values in sub-query so they are returned for each row in sub query.

Upvotes: 2

Hamidreza
Hamidreza

Reputation: 3118

Try this:

INSERT INTO `modules_templates`
(`module_template_id`,`module_template_modified`,`module_id`,`template_id`) 
SELECT '','2014-04-14 10:07:03','300',template_id FROM templates WHERE 1

Upvotes: 1

Kraang Prime
Kraang Prime

Reputation: 10479

To add more than one row, the field count/type must match :

INSERT INTO foo SELECT * FROM bar;

Please see the following link on how to do this properly :

http://www.w3schools.com/sql/sql_insert_into_select.asp

Upvotes: 0

Andrzej Reduta
Andrzej Reduta

Reputation: 767

INSERT INTO `modules_templates`
(`module_template_id`,`module_template_modified`,`module_id`,`template_id`) 
VALUES  
('','2014-04-14 10:07:03','300',
     (SELECT template_id FROM templates WHERE 1 limit 1))

Upvotes: 0

Related Questions