mysql insert on duplicate FIELD instead of KEY

Question

------------------------------------- | user_id | user_name | user_visits | ------------------------------------- | 1       | foo       | 5           | ------------------------------------- | 2       | bar       | 12          | -------------------------------------

user_id: auto increament, user_visits: default 1

INSERT INTO table (user_name) VALUES ('baz'), ('bar'), ('qux');

the above statement will of course insert 3 new records, as the result:

------------------------------------- | user_id | user_name | user_visits | ------------------------------------- | 1       | foo       | 5           | ------------------------------------- | 2       | bar       | 12          | ------------------------------------- | 3       | baz       | 1           | ------------------------------------- | 4       | bar       | 1           | ------------------------------------- | 5       | qux       | 1           | -------------------------------------

but what I'm trying to achieve is:

------------------------------------- | user_id | user_name | user_visits | ------------------------------------- | 1       | foo       | 5           | ------------------------------------- | 2       | bar       | 13          | ------------------------------------- | 3       | baz       | 1           | ------------------------------------- | 4       | qux       | 1           | -------------------------------------

so literally,

if field user_name exists, update user_visits, else insert a new record.

is it possible to achieve this with a single insert statement?

Answer 1

Sure there is but it has nothing to do with your insert statement. You need to add a unique index on the user_name column:

create unique index user_name_idx on yourtable (user_name); 

Then afterward in your code that tracks the count will have to decide whether to do an insert or an update.

Answer 2

You have to create a key for your username field and then use INSERT ON DUPLICATE query to update the columns values.

For example your query must be,

   INSERT INTO table (user_name) VALUES ('baz'), ('bar'), ('qux')     ON DUPLICATE KEY UPDATE user_visits=user_visits+1; 

For further reference visit http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html