Nicster15 Nicster15 - 2 years ago 73
SQL Question

MySQL / SQL Incrementing a Column

Im trying to keep track of points / stats by making a column auto increment. However, it's not working as I want it. I want it to auto increment if the row gets updated, not if a new row gets added. For example, if I run the update command it will just add one to the "count" column for the row I updated. If I add a new row it'll start at 0!

Here is my code to create a table:

statement = connection.prepareStatement(
"(" +
"id varchar(100) not null," +
"count int not null auto_increment," +
"PRIMARY KEY (id)," +
"KEY (count)" +

Here is how I update to a specific row:

connection = plugin.getHikari().getConnection();
statement = connection.prepareStatement("INSERT INTO stats (id) VALUES(?) ON DUPLICATE KEY UPDATE id=?");
statement.setString(1, id.toString());
statement.setString(2, id.toString());

- Nicster

Answer Source

1) Remove the AUTO_INCREMENT attribute from the `count` column.

AUTO_INCREMENT isn't a suitable mechanism for what you are trying to achieve.

2) Add a DEFAULT 1 to the definition of the `count` column

When a new row is inserted into the table, and a value is not supplied for the `count` column, the default value will be assigned to the column.

3) re-write the INSERT statement to increment the `count` column when an attempt is made to add a duplicate `id` value

 INSERT INTO atickets_stats (id)
 VALUES ( ? )
 UPDATE count = count + 1 ;  
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download