CODEWITHSUNDEEP
javamysql
Rajeswari Kotikalapudi
Rajeswari Kotikalapudi

Reputation: 592

How to Auto increment a column in mysql table through java

i have table with 2 columns 1.column1 2.column2(its unique) now through java coding i am inserting data through 2 methods in the first method i want to insert data ,in this coumn1 filed should be auto increment(for new user)

String sql = "INSERT INTO table (column1, column2) values(?, ?)";
pstm = connection.prepareStatement(sql);

    pstm.setInt(1, auto_incrmentvalue need to set);
    pstm.setInt(2,column2);

in the second method insert data with what i want 

String sql = "INSERT INTO table (column1, column2) values(?, ?)";
pstm = connection.prepareStatement(sql);

    pstm.setInt(1, column1);
    pstm.setInt(2,column2);

how to set auto increment value in the first method

NOTE:Here column1 is not a primary key

Upvotes: 3

Views: 6557

Answers (3)

Quicksilver
Quicksilver

Reputation: 2700

INSERT INTO table(column1) SELECT MAX(column1)+1 FROM table

This one worked for me

Please see INSERT...SELECT

Your query should be like this,

INSERT INTO table(column1, column2) SELECT MAX(column1)+1, 79  FROM table

More refined answer:

INSERT INTO
     usertable(column1, column2) 
     SELECT CASE COUNT(column1) 
         WHEN 0 THEN 0 
         ELSE MAX(column1) END+1,
     79 FROM usertable

This could be a more simple solution:

INSERT INTO usertable(column1, column2) 
SELECT IFNULL(MAX(column1)+1,1),79 FROM usertable

Upvotes: 5

Learner
Learner

Reputation: 714

Lets try this once (not tested)

INSERT INTO table(column1) SELECT count(column1)+1 FROM table

For ex,

INSERT INTO table(column1, column2) SELECT count(column1)+1, 79  FROM table

Upvotes: 0

Puggan Se
Puggan Se

Reputation: 5846

if you want the auto value of column1, don't set it, just let it get a defult value

INSERT INTO table (column2) values(?)

Upvotes: -1

Related Questions