mardi 2 juin 2015

Multiple inserts into SQLite getting data from other tables

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.

Aucun commentaire:

Enregistrer un commentaire