0
INSERT INTO `Employees` 
        (employeeFirstName, employeeLastName, managerID, employeeRole) 
VALUES ("Kim", "Brooks", NULL, "Manager");

INSERT INTO `Employees` 
        (employeeFirstName, employeeLastName, managerID, employeeRole) 
VALUES ("Alice", "Brown", 
        (SELECT employeeID 
        FROM Employees 
        WHERE employeeFirstName = "Kim" 
        and employeeLastName = "Brooks"
        ), "Instructor"
    );

I have the above code. However, for my second insert statement, it gives me an error stating "Table 'Employees' is specified twice, both as a target for 'INSERT' and as a separate source of data.

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Josh H
  • 1
  • True - what are you trying to do? – P.Salmon Mar 10 '22 at 16:25
  • I am trying to make it so that the person in the second insert statement has, in their managerID field, the employeeID of the person from the first insert statement. – Josh H Mar 10 '22 at 16:30
  • use a subselect see https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b84ce4204ecaa1847298f2fb3d1ad79a – nbk Mar 10 '22 at 16:31
  • If employees has an auto_increment id it may be possible to capture the last inserted id and use that to identify which manager should be assigned, – P.Salmon Mar 10 '22 at 16:40
  • https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b3cb993b7908240b3ae10baf2da23ad1 – P.Salmon Mar 10 '22 at 16:54

0 Answers0