CODEWITHSUNDEEP
javascriptmysqlsqlnode.js
shmnsw
shmnsw

Reputation: 649

MySql increment field for each key value insertion and handle duplicates as well

Background

I have a table with two columns, the one is the key, which is word and the other is not a key, and it's instances. I want to insert each word to a new row, and increment it's instances field by 1. Now, if the word that being inserted now is already exist, it should not be duplicated but it's instances filed should be incremented.

Example

  1. table is empty at start
  2. inserting the word "hello" and increment it's instances by 1.
  3. inserting the word "world" and increment it's instances by 1.
  4. inserting the word "hello" again and increment it's instances by 1.

So now the table will look like this: 

hello   2
world   1

I have tried:

MySql_Connection.query("

    INSERT INTO `my_database`.my_table 
    (`word`) VALUES ('"+ word_to_insert +"') 
    UPDATE  `instances` = `instances` + 1 
    ON DUPLICATE KEY `instances` = `instances` + 1

");

getting a syntax error

Upvotes: 0

Views: 286

Answers (1)

VMai
VMai

Reputation: 10346

Your statement should be

MySql_Connection.query("

    INSERT INTO `my_database`.my_table 
    (`word`) VALUES ('"+ word_to_insert +"') 
    ON DUPLICATE KEY UPDATE `instances` = `instances` + 1

");

instead.

You should provide an DEFAULT value for your instances column too, to get the value of 1 in your instances column if inserting a new word.

CREATE TABLE my_table (
    word VARCHAR(100) NOT NULL PRIMARY KEY,
    instances INT NOT NULL DEFAULT 1
);

Demo

Upvotes: 1

Related Questions