0

I need to insert data into a mysql table in one query. The query inserts more than 1 record, and the 2nd record needs to get the id of the first one and populate it in a parentid column. I'm new at scripting queries, and I have no idea how to accomplish this.

example:

| id | parentid |
| 1  | null     |
| 2  | 1        |
kroe761
  • 3,296
  • 9
  • 52
  • 81

2 Answers2

1

No, it cannot be done in a single query.

MySQL does not implement the standard SQL feature of "deferrable constraints" that would be necessary for this query (INSERT) to succeed. A solution is possible in PostgreSQL or Oracle, however.

This is not possible to achieve in MySQL since during the insertion of the second row, its foreign key constraint will fail because the first row does not yet "officially" exist -- though was inserted. However, if the FK constraint check is deferred until the end on the SQL statement (or until the end of the transaction), the query would complete successfully... but that's not implemented in MySQL.

The Impaler
  • 45,731
  • 9
  • 39
  • 76
  • This is for an api that would insert multiple records into the database in one call. Is performing this action in multiple queries acceptable? – kroe761 Mar 08 '19 at 20:24
  • Yes, multiple inserts will work. You can even use a "single call" to a stored procedure that inserts multiple rows. – The Impaler Mar 08 '19 at 20:24
1

You could use LAST_INSERT_ID, but I don't think this would be considered "one query":

START TRANSACTION;
BEGIN;
INSERT INTO tablename (parent_id) VALUES
(NULL);
INSERT INTO tablename (parent_id) VALUES
(LAST_INSERT_ID());
COMMIT;
Lord Elrond
  • 13,430
  • 7
  • 40
  • 80