Inserting values based on results of another insert

Question

There are two tables in mysql database academy and instructors. I want to insert values to each table in the following order(pseudo code):

1st INSERT INTO academy values acad_name, address, street, city, state
2nd INSERT INTO instructors values instructor_name
3rd INSERT INTO academy values instructor_id FROM instructors \this records should go with the corresponding academy_id

The third insert is based on the results of the second insert instructor_id which is auto_increment. But I want the third insert to correspond to the current academy the values where inserted to previously how can I do so?

CREATE TABLE IF NOT EXISTS `academy` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(25) NOT NULL,
  `academy_id` int(11) NOT NULL,
  `instructor_id` int(11) DEFAULT NULL,
  `street_address` varchar(50) NOT NULL,
  `city` varchar(25) NOT NULL,
  `state` varchar(25) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `instructor_id` (`instructor_id`) // Foreign KEY
);

CREATE TABLE IF NOT EXISTS `instructors` (
  `instructor_id` int(11) NOT NULL AUTO_INCREMENT,
  `academy_id` int(11) NOT NULL
  PRIMARY KEY (`instructor_id`),
  KEY `academy_id` (`academy_id`) // Foreign KEY
);

your table design is having circular FK! academy_id Foreign KEY in instructors and instructor_id is Foreign KEY in academy table. — Damodaran, Nov 14 '13 at 07:30
@Damodaran reason is for another question asked previously, [Stackoverflow](http://stackoverflow.com/questions/19959164/display-values-from-several-tables-that-share-one-foreign-key/19960089?noredirect=1#comment29710299_19960089) — Code_Ed_Student, Nov 14 '13 at 07:34

score 1 · Answer 1 · answered Nov 14 '13 at 07:30

1

You can use insert id function to retrive the id of the academy table and add into the other table insertion.

mysqli

mysqli_insert_id();

mysql

 mysql_insert_id();

answered Nov 14 '13 at 07:30

Krish R

22,583
7
50
59

score 1 · Accepted Answer · answered Nov 14 '13 at 07:47

Your table structure is a little confusing. Why have an auto incrementing 'id' field in table academy when you have an 'academy_id' field that would (I assume) be unique, use 'academy_id' as the primary key.

The table structure implies a many-to-many relationship with each table having a foreign key constraint for the other table, assuming under normal circumstances that an instructor belongs to only one academy but an academy can have many instructors you should remove the foreign key constraint from table academy and then just reference all the instructors belonging to that academy from the foreign key constraint in table instructors.

If the scenario implies that there must be a many-to-many relationship then you must create an intersection entity. Meaning that if a academy has many instructors and an instructor can have many academies then you create a new table with two foreign key constraints from table academy and instructor with a unique id field as well for the new table.

score 0 · Answer 3 · edited May 23 '17 at 10:25

0

First insert the entry in academy table and then in to instructors table. Then use

mysql_insert_id

get the last inserted id and update the entry in academy table. Check this question

edited May 23 '17 at 10:25

Community

1
1

answered Nov 14 '13 at 07:35

Damodaran

10,882
10
60
81

score 0 · Answer 4 · answered Nov 14 '13 at 07:40

You can query the value of the auto increment column after each insert, e.g.

SELECT @insert_id:=`AUTO_INCREMENT`
FROM  INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'YourDatabaseName'
AND   TABLE_NAME   = 'YourTableName';
)

or

INSERT INTO `academy`(`id`) VALUES (NULL);
SET @last_insert_id = LAST_INSERT_ID();

set that to a user defined variable and then use it on the consecutive inserts.

However if multiple - parallel inserts take place you must also use lock tables in order to use correct autoincrement id values.

http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

Inserting values based on results of another insert

4 Answers4