How to Auto increment a column in mysql table through java

Question

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

@QuickSilver - no. Did you not read what the bolded part says? — N.B.
– N.B., Commented Jul 11, 2012 at 8:57

Quicksilver · Accepted Answer · 2012-07-11 11:00:26Z

5

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

1 Comment

Rajeswari Kotikalapudi Over a year ago

i try like this it give error mysql> insert into userstable(column1, column2) values ((select max(column1) from table)+1, 79); ERROR 1093 (HY000): You can't specify target table 'userstable' for update in FROM clause

Learner · Accepted Answer · 2012-07-11 10:00:53Z

0

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

answered Jul 11, 2012 at 10:00

Learner

7141 gold badge5 silver badges24 bronze badges

1 Comment

Rajeswari Kotikalapudi Over a year ago

its working fine i have one more doubt suppose we have 9 columns when one column is deleted then again it stores 9 for the column1 or not?

Puggan Se · Accepted Answer · 2012-07-11 08:53:41Z

-1

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

INSERT INTO table (column2) values(?)

answered Jul 11, 2012 at 8:53

Puggan Se

5,8462 gold badges25 silver badges51 bronze badges

Collectives™ on Stack Overflow

How to Auto increment a column in mysql table through java

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related