2

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);
luser droog
  • 18,988
  • 3
  • 53
  • 105
Austin Johnston
  • 219
  • 1
  • 4
  • 10
  • Show your queries. What exactly is the problem? Do you have errors? – Tchoupi Mar 04 '13 at 04: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 Mar 04 '13 at 04: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 Mar 04 '13 at 04: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 Mar 04 '13 at 04:35

1 Answers1

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.

Community
  • 1
  • 1
Danny Beckett
  • 20,529
  • 24
  • 107
  • 134
  • 1
    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. – Austin Johnston Mar 04 '13 at 04:41