SQL inserting last ID from another table-CodePudding

I am currently using a web app and am trying to insert records into the Database from user info. We have 3 tables, Item, User, Item_Status_History

Item_Status_History takes in Item_ID (the pk from Item) and User_ID from user. I run two queries to insert new records from a user before running the Item_Status_History insert, i need to grab the MAX(Item_ID) and MAX(User_ID) from the previous just inserted records to insert into Item_Status_History.

Here is the query i have tried but does not work

INSERT INTO Item_Status_History(Item_FK, User_FK, Status_FK, ISH_Date, ISH_Time, ISH_Location) 
SELECT MAX(Item_ID), MAX(User_ID) from Item i, User u 
values (i.Item_ID, u.User_ID, 'Lost', 2022-01-01, 3:00, "Test")

CodePudding user response：

You can't use both SELECT and VALUES as sources in the same INSERT query, it's one or the other.

That said, don't use MAX(), as this could change if another process inserts another user or item (although this is mitigated if you put all the queries in a transaction). The function LAST_INSERT_ID() returns the ID that was assigned by the last INSERT query in the current session, this is the preferred way to assign foreign keys.

And another syntax issue: The date and time need to be in quotes.

INSERT INTO User ...;
SET @user_id = LAST_INSERT_ID();
INSERT INTO Item ...;
SET @item_id = LAST_INSERT_ID();

INSERT INTO ItemStatus_History (Item_FK, User_FK, Status_FK, ISH_Date, ISH_Time, ISH_Location) 
VALUES values (@item_id, @user_id, 'Lost', '2022-01-01', '3:00', "Test");

CodePudding user response：

Here I have created a test schema and modified your syntax.
The insert is now working.

create table Item_Status_History
(Item_FK int, User_FK int, Status_FK varchar(10), ISH_Date date, ISH_Time time, ISH_Location varchar(10));
create table Item(Item_ID int);
create table User(User_id int);
insert into Item values(10);
insert into User values(20);

INSERT INTO Item_Status_History
(Item_FK, User_FK, Status_FK, ISH_Date, ISH_Time, ISH_Location) 
SELECT 
  (select MAX(Item_ID)from Item ), 
  (select MAX(User_ID) FROM User) , 
  'Lost', 
  '2022-01-01', 
  '3:00', 
  "Test"

select * from Item_Status_History;

Item_FK | User_FK | Status_FK | ISH_Date   | ISH_Time | ISH_Location
------: | ------: | :-------- | :--------- | :------- | :-----------
     10 |      20 | Lost      | 2022-01-01 | 03:00:00 | Test

db<>fiddle here