"insert into" syntax using columns from another table and new values

Question

I have a student table and a takes table. The takes table is for courses that students have taken or are currently taking, and has columns:

ID, course_id, sec_id, semester, year, grade

I want to use ID from student but then for the other 5 columns use:

(CS-001, 1, Fall, 2009, NULL)

I know how to use insert into takes select, from, where, and also insert into takes values() But I have not seen anything that mixes data from another table and also new data that I put in. The labs we are doing are supposed to be done all in MySQL queries, using phpMyAdmin.

I am poor at explaining things so I am sorry if this does not make sense. Thank you for your help.

Here is the sort of query I want:

insert into takes(ID, course_id, sec_id, semester, year, grade)
values(*IDs from student table*, CS-001, 1, Fall, 2009, NULL);

Show your queries. What exactly is the problem? Do you have errors? — Tchoupi
– Tchoupi, Commented Mar 4, 2013 at 4:23
Not sure what to try for a query. So I have a table 'takes' with columns (ID, course_id, sec_id, semester, year, grade) I want to insert rows that use ID from another table but the rest of the columns will be (CS-001, 1, Fall, 2009, NULL) — Austin Johnston
– Austin Johnston, Commented Mar 4, 2013 at 4:25
Do you need to insert a student and using the inserted ID in a insert query for 'takes'?. You could use set @ var1 = (select col from table where filter) and then use @ var1 in the insert statement — Esselans
– Esselans, Commented Mar 4, 2013 at 4:27
The student already exists in another table 'student'. I need to take the ID's from that table where dept_name = Comp. Sci. and add those students 'ID's into 'takes' but for the other columns in the newly inserted rows to 'takes' I need to use the values I gave. — Austin Johnston
– Austin Johnston, Commented Mar 4, 2013 at 4:35

Community · Accepted Answer · 2017-05-23 12:14:00Z

6

Something like this should fill up your takes table with data from your student table, and the static values you want for the rest of the columns:

INSERT INTO takes (ID, course_id, sec_id, semester, year, grade)
SELECT      ID, 'CS-001', 1, 'Fall', 2009, NULL
FROM        student
WHERE       dept_name = 'Comp. Sci.';

Take a look at this question for more help.

edited May 23, 2017 at 12:14

CommunityBot

11 silver badge

answered Mar 4, 2013 at 4:29

Danny Beckett

21.1k27 gold badges114 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Austin Johnston Over a year ago

This worked perfectly! Thank you so much. I did not know that SQL would be able to tell the difference between a literal and a field in another table. For example ID being a field from another table and 1 being a literal int. Thanks again.

Collectives™ on Stack Overflow

"insert into" syntax using columns from another table and new values

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related