How do you insert information in a table if you have a unary relationship-CodePudding

create table Employee(
    staff_id_employee int primary key ,
    gender char(1) check (gender in('F','M')),
    staff_id int foreign key REFERENCES Staff(staff_id),
    manager_id int foreign key references Employee(staff_id_employee)
);

How do I insert the information in this table.The text to this is that an employee manages other employees.If anyone could give me some sort of example cuz I don't know what to write instead of the question marks '????'.WHat is the value to manager_id in this case

INSERT into Employee(staff_id_employee, gender, staff_id, manager_id) VALUES (30000,'F',6007,????)

CodePudding user response：

If you need to look-up the manager's Id then you want an insert select

INSERT into Employee(staff_id_employee, gender, staff_id, manager_id) 
SELECT 30000,'F',6007, staff_id_employee
from Employee
where staff_id = [Manager's staff ID]

CodePudding user response：

If Employee 3000 doesn't have a manager, you would typically insert a null

INSERT into Employee(staff_id_employee, gender, staff_id, manager_id) 
             VALUES (30000,'F',6007, null)

Otherwise insert the manager's ID.