Using sql to keep track of words and their count

Question

I have a situation where a user enters certain words at a time,say {bat,ball,tennis,car,actor,ping}.I have a database with the following structure

------------------------------
word (PK)    |    count 
------------------------------
   ball      |     4
   cat       |     2
   gear      |     1
             |

I want to insert each word into the table .If the word is already present,increment the counter by 1 else insert the word (as it is new) and set its count to 1.

Is it possible using a single query?If yes, how can I do it?

Is {bat,ball,tennis,car,actor,ping} actually the format of the input string? Any particualt flavor of SQL RDBMS? — Karl Kieninger, Mar 09 '14 at 15:41
@KarlKieninger:Actually the user enters the string of keywords and I am exploding it on the basis of space `" "` to get individual words and then inserting them.I am using MySQL — Naveen, Mar 09 '14 at 15:43
Here is link you can read on MERGE function in MySQL: http://www.xaprb.com/blog/2006/02/21/flexible-insert-and-update-in-mysql/ — Dipendu Paul, Mar 09 '14 at 16:03

score 0 · Answer 1 · answered Mar 09 '14 at 15:52

If your word column is truly the primary key, you should be able to do something like this.

INSERT INTO table_name (`word`, `count`) VALUES("ball", 1) 
    ON DUPLICATE KEY UPDATE `count` = `count` + 1

Pretty straight forward taking advantage of the database to perform the update in the database layer.

score 0 · Answer 2 · edited May 23 '17 at 12:05

Normally I avoid answer that amount to links, but in this case I think the question is really just a two-parted, each of which has been asked here before.

There are two steps you have to do.

You have to split your keywords set into a table of some flavor, Last I knew MySQL did not have a split strings, but how to do it has been asked several times on SO. See Can Mysql Split a column?
Then you can use INSERT...ON DUPLICATE as discussed in "How do I update if exists, insert if not (AKA “upsert” or “merge” in MySQL?"

Using sql to keep track of words and their count

2 Answers2