MySQL and inserting rows only if they does not exist

Discussion in 'MySQL' started by xms, Aug 12, 2017.

  1. #1
    I have MySQL database with the following table:

    CREATE TABLE points
    (
      competition_id INT(10) UNSIGNED NOT NULL
      , user_id INT(10) UNSIGNED NOT NULL
      , pts INT(10) NOT NULL
    
      , PRIMARY KEY (competition_id, user_id)
    
      , INDEX (competition_id)
      , INDEX (user_id)
    ) ENGINE=InnoDB CHARACTER SET latin1 COLLATE latin1_swedish_ci;
    Code (SQL):
    I should insert several rows. If a row exists, it must be skipped. If a row does not exist, it must be inserted. It is not allowed to modify the existing rows.

    I have a couple of ideas, but will these work at all? How would you do this?

    1)

    INSERT IGNORE INTO points (competition_id, user_id, pts)
    VALUES (1, 2, 3), (4, 5, 6)
    Code (SQL):
    2)

    INSERT INTO points (competition_id, user_id, pts)
    VALUES (1, 2, 3), (4, 5, 6)
    ON DUPLICATE KEY UPDATE competition_id = competition_id
    Code (SQL):
     
    Last edited: Aug 12, 2017
    xms, Aug 12, 2017 IP