Multiple inserts into SQLite getting data from other tables

Question

In SQLite, I have a simple message board where I want to timestamp when each user first sees any post.

CREATE TABLE messages (
    message_id INTEGER PRIMARY KEY,
    mb_topic_id INTEGER NOT NULL REFERENCES mb_topics(mb_topic_id),
    message_poster INTEGER NOT NULL REFERENCES users(user_id),
    message_content TEXT,
    message_post_time TIMESTAMP DEFAULT (strftime('%s', 'now'))
)

CREATE TABLE messages_seen (
    message_id INTEGER NOT NULL REFERENCES messages(message_id),
    user_id INTEGER NOT NULL REFERENCES users(user_id),
    seen_time TIMESTAMP DEFAULT (strftime('%s', 'now')),
    UNIQUE (message_id, user_id)
)

What I want to do, in one SQL statement if possible is when a user loads a message thread, to INSERT OR IGNORE into messages_seen their userID (which I have) for each message_id matching the mb_topic_id (which I have).

So if the user_id is 4 and the mb_topic_id is 7, I could do something like:

SELECT message_id FROM messages WHERE mb_topic_id = 7

And that could return something like (9,11,14,26). And from that I'd do:

INSERT OR IGNORE INTO messages_seen (message_id, user_id) VALUES (9,4);
INSERT OR IGNORE INTO messages_seen (message_id, user_id) VALUES (11,4);
INSERT OR IGNORE INTO messages_seen (message_id, user_id) VALUES (14,4);
INSERT OR IGNORE INTO messages_seen (message_id, user_id) VALUES (26,4);

Is there a way to squeeze that into one statement? BTW, the server that I'm running this on is running SQLite 3.6.20.

Mureinik · Accepted Answer · 2015-06-02 20:03:10Z

1

[EDIT: Irrelevant suggestion about SQLite 3.7.11 removed]

You could use insert-select with the user id as a literal:

INSERT OR IGNORE INTO messages_seen (message_id, user_id) 
SELECT message_id, 4
FROM   messages 
WHERE  mb_topic_id = 7

edited Jun 2, 2015 at 20:03

answered Jun 2, 2015 at 19:52

Mureinik

316k54 gold badges398 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dan Goodspeed Over a year ago

I did see the thing about 3.7... which is why I specified that the server I'm using is running 3.6.

Mureinik Over a year ago

@DanGoodspeed I missed that in the OP for some obscure reason. I'll remove that from my answer.

Mr. Llama · Accepted Answer · 2015-06-02 20:03:39Z

1

SQLite supports using SELECT statements in INSERT statements.

For example:

INSERT OR IGNORE INTO messages_seen (message_id, user_id)
SELECT message_id, user_id
FROM ...

edited Jun 2, 2015 at 20:03

answered Jun 2, 2015 at 19:51

Mr. Llama

21k3 gold badges70 silver badges121 bronze badges

2 Comments

Dan Goodspeed Over a year ago

I'm not sure I follow the statement there... selecting user_id from messages? There is no user_id in messages.

Mr. Llama Over a year ago

@DanGoodspeed - My apologies. I meant it merely as an example. You'll simply use whatever query returns the records you intend to insert.

Collectives™ on Stack Overflow

Multiple inserts into SQLite getting data from other tables

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related