SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    determining the existence of a record before adding one

    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.

    Code:
    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

    Code:
    insert into t1 (f1, f2) values (1, 2) where not exists (select * from t1 where userid = 35);

  2. #2
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    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.

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  4. #4
    SitePoint Addict
    Join Date
    Feb 2004
    Location
    Staffordshire, UK & Florida, USA
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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).

  5. #5
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Excellent, thanks so much for the input. I appreciate it.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •