I have an innodb table called word_frequency with 4 columns
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
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.