CODEWITHSUNDEEP
javasqlsqliteinsertprepared-statement
Kijewski
Kijewski

Reputation: 26043

Use ID inserted with `INSERT OR IGNORE` in next `INSERT` statement

I use sqlite 3.7 and have a schema that looks like:

CREATE TABLE IF NOT EXISTS subjects (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);

CREATE TABLE IF NOT EXISTS predicates (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
    predicate TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS objects (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
    object BLOB NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS statements (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
    subject INTEGER NOT NULL REFERENCES subjects(id) ON DELETE RESTRICT, 
    predicate INTEGER NOT NULL REFERENCES predicates(id) ON DELETE RESTRICT, 
    object INTEGER NULL REFERENCES objects(id) ON DELETE RESTRICT
);

To insert a row into statements I first INSERT OR IGNORE the predicate and the object, then I retrieve the ids:

INSERT OR IGNORE INTO predicates(predicate) VALUES (?)

SELECT predicates.id FROM predicates WHERE predicates.predicate = ?

To finally execute

INSERT INTO statements(subject, predicate, object) VALUES (?, ?, ?)

I my question is: Is there a way to directly do these (prepared) statements in one go?

INSERT OR IGNORE INTO predicates(predicate) VALUES (?);
SELECT predicates.id FROM predicates WHERE predicates.predicate = ?;
INSERT OR IGNORE INTO objects(object) VALUES (?);
SELECT predicates.id FROM objects WHERE objects.object = ?;
INSERT INTO statements(subject, predicate, object) VALUES (?, ?, ?);

Upvotes: 0

Views: 63

Answers (1)

CL.
CL.

Reputation: 180260

last_insert_rowid() works only if some row was actually inserted.

You always might need the SELECTs, but you can do them as subqueries:

INSERT OR IGNORE INTO predicates(predicate) VALUES (?);
INSERT OR IGNORE INTO objects(object) VALUES (?);
INSERT INTO statements(subject, predicate, object)
VALUES (?,
        (SELECT id FROM predicates WHERE predicate = ?),
        (SELECT id FROM objects WHERE object = ?)
       );

Upvotes: 1

Related Questions