1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

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