I had no idea to word the Question but here goes the details of my question:
(Please feel free to reword the question if anything is not clear or clearly addressed)
I want to know if it is possible to write to 2 tables
sequentially from a single MySql statement. Here is what I am trying to achieve:
- I have two tables: Table A (Employee) and Table B (Managers).
* Table A stores all the Employee particulars including the Primary Key (manID) of Table B while Table B stores only the following.
(Table B layout:
manID INT(11) Primary Key
FK_empID INT(11)
- An employee can be both an Employee and a Manager.
If an Employee is also a Manager, I was hoping to Insert the new Employee into Table A, get the newly created empID (using
SELECT LAST_INSERT_ID()
) to Insert the empID into Table B (FK_empID), then return the newly created manID to Table A, FK_manID.
It is recursive, write to Table A then to Table B then back to Table A.
What I am hoping for, if it is firstly advisable and secondly doable, to use a single SQL statement to achieve all of this instead of using 3 statements, 2x Insert's and 1x Update.
However, there's probably a chance that a more simplified method of achieving this without duplicating information across various tables.
The goal is to be able to store Employee information in Table A, and if an Employee is a Manager have it referenced in Table B. When an Employee is working under a Manager, the Manager's information can be linked back to the Employee information stored in Table A.
Regards,
T
What I have tried:
Insert new Employee, get new empID with "SELECT LAST_INSERT_ID()". Insert empID from Table A to FK_empID in Table B. Get the newly created manID in Table B and then Update Table A FK_manID.