Inserting Persons with IDs in one query?

Question

I need to add data to a MySQL database like that:

Person: pId, nameId, titleId, age

Name: nameId, name

Title: titleId, title

I don't want to have any names or title more then once in the db so I didn't see a solution with LAST_INSERT_ID()

My approach looks like that:

INSERT IGNORE INTO Name(name) VALUES ("Peter");
INSERT IGNORE INTO Title(title) VALUES ("Astronaut");
INSERT INTO Person(nameId, titleId, age) VALUES ((SELECT nameId FROM Name WHERE name = "Peter"), (SELECT nameId FROM Name WHERE name = "Astronaut"), 33);

But I guess that's a quite dirty approach!? If possible I want to add multiple persons with one query and without having anything more then one times in db. Is this possible in a nice way? Thanks!

why is the column 'name' and 'title' not in one table ? Then using last_insert_id() would be way easier. — KarelG, Aug 10 '13 at 12:15
possible duplicate of [Design dielema; If e-mail address already used send email "e-mail address already regsitered" but can't because can't add duplicate to table](http://stackoverflow.com/questions/18154031/design-dielema-if-e-mail-address-already-used-send-email-e-mail-address-alread) — Sylvain Leroux, Aug 10 '13 at 12:17
@KarelG because then if I would add "Peter", "Astronaut2" and "Peter", "Astronaut3" and "Peter", "Astronaut4" for example I would always add "Peter" to db. I want to store "Peter" just one times in the db and having the id as alias for all other peters afterwards. Or isn't that right? I though you should save as much space as possible... — user2170547, Aug 10 '13 at 12:38

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

You could put title and name as two columns of your table and then:

set one UNIQUE index on each column if you don"t want to have two titles or two names identical in the DB
or set an UNIQUE index on (title,name) if you don't want to have two entries having both the same name and the same title.

If you really want to have separate tables, you could do as you suggested in your post, but wrapping all your insert statements in a TRANSACTION to allow rollback if you detect a duplicate somewhere.

See Design dilemma: If e-mail address already used, send e-mail "e-mail address already registered", but can't because can't add duplicate to table which appear to be exactly the same problem, but having name & email instead of name & titles.

START TRANSACTION;
INSERT INTO title(value) VALUES ("Prof.");
SELECT LAST_INSERT_ID() INTO @title_id; 
    -- Instead of using user-defined variable, 
    -- you should be able to use the last_insert_id 
    -- equivalent from the host language MySQL driver.
INSERT INTO username(value) VALUES ("Sylvain");
SELECT LAST_INSERT_ID() INTO @username_id;
    -- Instead of using user-defined variable, 
    -- you should be able to use the last_insert_id 
    -- equivalent from the host language MySQL driver.

INSERT INTO account(username_id, email_id) VALUES (@username_id,@title_id); 
COMMIT;

See LAST_INSERT_ID()

A third solution would be to SELECT before doing you insert to see in the entry are already present. But personally I wouldn't push to the check-before-set approach at the very least, this will require an extra query which is mostly superfluous if you use correctly indexes.

I've read your linked post but I couldn't figure out if/how I can prevent the subselect in the last insert query with that? What I simple would like to do is this query: INSERT INTO Person(nameId, titleId, age) VALUES ("Peter", "Astronaut", 33) but with a replace of the strings by the associated ids. If there's no id for a name it should be stored in the other tables before. — user2170547, Aug 10 '13 at 12:42
@user2170547 I've edited my answer to show you how to do that in SQL-only. The key idea is, since you will have two inserts you will be able to use `last_insert_id()` after each `INSERT` to obtain each entry's id (assuming `auto_increment` columns). Here I use MySQL user-defined variable. In real world, you will probably use plain variables from your host language. — Sylvain Leroux, Aug 10 '13 at 12:52

Inserting Persons with IDs in one query?

1 Answers1