CODEWITHSUNDEEP
mysql
Chameron
Chameron

Reputation: 2974

MySQL - insert new id

I have to insert 3 records in one time:

INSERT INTO table (id, name, parent_id) 
VALUES ('1', 'a', 0), (2, 'b', 1), (3, 'c', 1);

What query exists so that I can insert 1st record's ID to parent_id of 2nd record and 3rd record?

INSERT INTO table (name, parent_id) 
VALUES ('a', 0), (2, 'b', SELECT table.id WHERE table.id=1), (3, 'c', SELECT table.id WHERE table.id=1);

Upvotes: 1

Views: 1571

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562260

You can't do this in a single query. You can use LAST_INSERT_ID() for the last value inserted by the most recent INSERT statement, but that doesn't help get your parent id values for the subsequent rows. Do it in two separate statements.

INSERT INTO table (name, parent_id) VALUES 
  ('a', NULL);
INSERT INTO table (name, parent_id) VALUES
  ('b', LAST_INSERT_ID()),
  ('c', LAST_INSERT_ID());

By the way, use NULL for the root node's parent, not 0. You don't have a row whose id is 0.

Upvotes: 4

Related Questions