1

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

jimmyboix
  • 117
  • 1
  • 8

1 Answers1

1

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

Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
  • This worked perfectly! makes sense now with your code snippet. I actually did the line CREATE UNIQUE INDEX UQ_WORD ON word_frequency (USER_ID, WORD); as I wanted to allow the same word to exist if another user chose to insert it. This seems to work fine. thanks @Giorgos Betsos – jimmyboix Feb 15 '16 at 10:37