sql - insert into a table with select

Question

I have two tables with these schema:

student(id,name,dept_name, tot_cred)
instructor(id,name,dept_name,salary)

and the question is:

Insert every student whose tot_cred attribute is greater than 100 as an instructor in the same department, with a salary of $10,000.

I try this query but I dont know how set salary for student $10,000:

insert into instructor (id,name,dept_name,salary) 
  select id,name,dept_name 
    from student 
   where tot_cred > 100

标签： sqlsql-insert

Answer 1

Just select the constant value. Personally, I'd add an alias in the select to document that the constant is intended to be the salary although it's not necessary. Different databases may have different rules for how to create that alias and you haven't told us what database you're using so I'm guessing on the syntax.

insert into instructor (id,name,dept_name,salary) 
  select id,name,dept_name,10000 as salary
    from student 
   where tot_cred > 100