Insert an identical value in a column for all the rows of a neighbouring column

Question

I have a select statement which fetches 100 rows of data and inserts it in a table. However, I want to insert to insert 1 into the other column for all the rows I have inserted in the neighbouring column.

This is my insert select statement

insert into examination_data (ed_cs_id,ed_examination_id) 
VALUES (( 
          select cs_id 
          from class_students 
          where cs_class_id = 1 AND cs_year_id = 1 ),1);

On running the query, I get this error

/* SQL Error (1242): Subquery returns more than 1 row */

You can find answer to your quesiton here: http://stackoverflow.com/questions/5391344/insert-with-select — Wirus, Jul 22 '13 at 06:42
First,when u try to insert into a table, remove the key word 'VALUES', Secondly, the number of columns which u want to insert does not match to the number of columns in your query. — Krishna Rani Sahoo, Jul 22 '13 at 07:19

score 0 · Accepted Answer · answered Jul 22 '13 at 06:38

0

insert into examination_data (ed_cs_id,ed_examination_id)  
          select cs_id,1 
          from class_students 
where cs_class_id = 1 AND cs_year_id = 1;

fiddle

answered Jul 22 '13 at 06:38

Praveen Prasannan

7,093
10
50
70

I get this error `Cannot add or update a child row: a foreign key constraint fails` – You Know Nothing Jon Snow Jul 22 '13 at 06:44
1

ed_examination_id might be a foreign key and you don't have data '1' in the related table. Please confirm – Praveen Prasannan Jul 22 '13 at 06:46
I do have 1 in a related table. – You Know Nothing Jon Snow Jul 22 '13 at 06:47
edit the question to show the create table out put for the two tables – Praveen Prasannan Jul 22 '13 at 06:49

score 0 · Answer 2 · answered Jul 22 '13 at 06:47

0

Try this

insert into examination_data (ed_cs_id,ed_examination_id)  (select cs_id,1 as "COLUMN_NAME" from class_students where cs_class_id = 1 AND cs_year_id = 1);

answered Jul 22 '13 at 06:47

Ravi

30,829
42
119
173

Insert an identical value in a column for all the rows of a neighbouring column

2 Answers2