I have a situation where I want to add a record to a table the first time a user performs a certain action. All subsequent actions will update that record. I know I can query the table to see if it exists, but was wondering if there was a way to do that with a single statement.
insert into t1 (f1, f2) values (1, 2) {if the record doesn't already exist for this user}
I tried a not exists as follows and it didn’t like it
insert into t1 (f1, f2) values (1, 2) where not exists (select * from t1 where userid = 35);
The proper way to model this is to key the table so that it doesn’t accept duplicates, and then use an ON DUPLICATE KEY UPDATE trigger to override key errors and log a count of executions rather than just a flag of whether it was done or not.
CREATE TABLE user_action (
user_id BIGINT UNSIGNED NOT NULL REFERENCES user(id),
action_id SMALLINT UNSIGNED NOT NULL REFERENCES action(id),
executions INT UNSIGNED NOT NULL,
first_execution TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
PRIMARY KEY (user_id, action_id)
) TYPE=InnoDB;
-- Then to record actions:
INSERT INTO user_action (user_id, action_id, executions)
VALUES (1, 1, 1)
ON DUPLICATE KEY UPDATE executions = executions + 1;
Actually, even better than that would be to store a history pipeline of all actions executed and when they were executed and use a VIEW to pull a list of who has done what in a boolean fashion. Always best to collect as much data as possible - cutting stuff out is always easier than adding…
Cheers.
If you want to insert a row only if it doesn’t already exist (based on a primary/unique key constraint), you can use INSERT IGNORE INTO. If you want to insert a row if it doesn’t already exist and overwrite it with new data if it does already exist, you can use REPLACE INTO (this will actually delete the row and then insert it again).
You’ve not said whether you’re using a programming language, but if you’re using PHP with a database abstraction layer like PDO, you can also catch exceptions and handle them in your code, for example trying to insert a row which violates a primary/unique key constraint (because a row already exists).