Search code examples
phpmysqldatabaserelational-databaseinnodb

Cannot get UPSERT to work on MYSQL with auto increment column and FK


I have an innodb table called word_frequency with 4 columns

  1. ID - INT (pk) (auto_increment)
  2. USER_ID - INT (fk)
  3. WORD - STRING
  4. FREQUENCY - INT

I have tried multiple different queries but cannot seem to perform an upsert properly. I only want to update the table when USER_ID and WORD both already exist, else new row. But It just keeps creating new rows with the auto increment ID.

INSERT INTO word_frequency (USER_ID, WORD, FREQUENCY)
    VALUES(1, "word", 32)
    ON DUPLICATE KEY UPDATE FREQUENCY = FREQUENCY + 27;

I even tried

INSERT INTO word_frequency (USER_ID, WORD, FREQUENCY)
        VALUES(1, "word", 32)
        ON DUPLICATE KEY UPDATE ID=LAST_INSERT_ID(ID), FREQUENCY = FREQUENCY + 27;

and

 INSERT INTO word_frequency (ID,USER_ID, WORD, FREQUENCY)
        VALUES(LAST_INSERT_ID(ID+1),1, "word", 32)
        ON DUPLICATE KEY UPDATE ID=LAST_INSERT_ID(ID), FREQUENCY = FREQUENCY + 27;

where am I going wrong here?

Any help would be appreciated


Solution

  • You have to create a unique index on WORD field like this:

    CREATE UNIQUE INDEX UQ_WORD ON word_frequency (WORD );
    

    This way MySQL knows that no duplicates are allowed on WORD. So, when INSERT tries to insert a word that already exists, then the UPDATE part of the query is executed.

    Demo here