How to insert many rows into two relative tables in mysql?

Question

I want to insert many rows into two relative tables. I used LAST_INSERTED_ID() to insert data into the second table, but this id does not change

BEGIN; 
insert into themes (tutorID, year, theme_name, degreeID) 
  select tutorID, year, work_name, degreeID from journal;
INSERT INTO assigned_students (studentID, tutorID, themeID, writing_language_id, work_typeID) 
  select studentID, tutorID, LAST_INSERT_ID(), 0, 4 from journal; 
COMMIT

Can you change the table names to the real names? I'm struggling to work out what the difference between tutored and table1id is - it looks like the id for table1 IS the tutoriid — Caius Jard, Jan 22 '19 at 12:35
BEGIN; insert into themes (tutorID, year, theme_name, degreeID) select themeID, year, work_name, degreeID from journal; INSERT INTO assigned_students (studentID, tutorID, themeID, writing_language_id, work_typeID) select studentID, tutorID, LAST_INSERT_ID(), 0, 4 from journal; COMMIT; — TIB, Jan 22 '19 at 12:41
When doing multiple-row inserts, MySQL LAST_INSERT_ID() will return the value of the first row inserted not the last row id that you except. To @CaiusJard, deleted answer and added as comment. — guneraykut, Jan 22 '19 at 12:47

Mad Dog Tannen · Answer 1 · 2019-01-22T12:51:52.143

Give this a try, where you declare a INT and then set that value, see below.

BEGIN;

declare lastid INT;

insert into table1 (tutorID, year, name, degreeID)
  select sm.tutorID, year, namework, dt.degreeID from table3;

set lastid = (SELECT LAST_INSERT_ID());

INSERT INTO table2 (studentID, tutorID, table1ID, writing_language_id, work_typeID)
  select distinct StudentID, tutorID, lastid , 0, 4 from table3;
COMMIT;

You can also try this.

BEGIN;

insert into table1 (tutorID, year, name, degreeID)
  select sm.tutorID, year, namework, dt.degreeID from table3;

INSERT INTO table2 (studentID, tutorID, table1ID, writing_language_id, work_typeID)
  select distinct StudentID, tutorID, (SELECT LAST_INSERT_ID()), 0, 4 from table3;
COMMIT;

I have the error " or DELIMITER expected, got 'declare'" – TIB Jan 22 '19 at 12:49 — TIB, Jan 22 '19 at 12:49

Caius Jard · Answer 2 · 2019-01-22T13:01:25.473

Make the inserts into assigned_students reference the just-inserted rows from themes (so you can get their auto generated IDs) also:

BEGIN; 
  insert into themes (tutorID, year, theme_name, degreeID) 
    select themeID, year, work_name, degreeID from journal; 
  INSERT INTO assigned_students (studentID, tutorID, themeID, writing_language_id, work_typeID) 
    select j.studentID, j.tutorID, t.THEME_ID_COLUMN_NAME, 0, 4 
    from 
      journal j 
      inner join themes t on j.themeID = t.tutorid; 
COMMIT

Here we see that first we make some inserts into themes, and I presume it will autogenerate some IDs for themes' pk column.

So we then join journal onto those rows we inserted so we can retrieve the generated IDs

I don't know what the PK column of themes is called, so you'll have to replace THEME_ID_COLUMN_NAME with the correct value

Note that you might have to specify additional columns in on j.themeID = t.tutorid than just the tutorid, if that doesnt uniquely identify a row

I read also that for autoincrement columns it is guaranteed that the IDs are sequential, so you can get the LAST_INSERTED_ID() which is the mos trecently inserted row) as well as the ROW_COUNT and hence know the range of IDs that was inserted, and use that to select/join the journal data

How to insert many rows into two relative tables in mysql?

2 Answers2