CODEWITHSUNDEEP
javamysqlsqldatabase
Nicster15
Nicster15

Reputation: 169

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(
                "CREATE TABLE IF NOT EXISTS stats" +
                        "(" +
                        "id varchar(100) not null," +
                        "count int not null auto_increment," +
                        "PRIMARY KEY (id)," +
                        "KEY (count)" +
                        ")"
        );
        statement.execute();

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());
                statement.execute();

Thanks, - Nicster

Upvotes: 1

Views: 805

Answers (4)

spencer7593
spencer7593

Reputation: 108500

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 ( ? )
 ON DUPLICATE KEY
 UPDATE count = count + 1 ;

Upvotes: 0

maresa
maresa

Reputation: 641

I'm not sure why you're having that problem. You're close, IMHO. All you need to do is to make count as INT NOT NULL default 0. Then you should always do INSERT ON DUPLICATE KEY UPDATE like below:

CREATE TABLE IF NOT EXISTS atickets_stats (
    `id` VARCHAR(100) NOT NULL,
    `count` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`)
);

INSERT INTO atickets_stats (id) VALUES(1) ON DUPLICATE KEY UPDATE count = count + 1;

Note, I changed the count column to be INT(11) UNSIGNED because I assume you won't ever store negative value here. You can take out UNSIGNED if you'll have negative value.

Upvotes: 0

Manish Sapkal
Manish Sapkal

Reputation: 6251

Use Before update trigger in this case. Set default value to 1. and update/increment it by 1 on every update using trigger.

Upvotes: 1

derlarsschneider
derlarsschneider

Reputation: 296

From mysql doc (http://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html):

The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows

So you have to chose a different approach:

  1. Read current value from database
  2. Prepare your statement updating also the count value

Problem: Concurrency You have to synchronize your method call to make it thread-safe.

Upvotes: 0

Related Questions