CODEWITHSUNDEEP
javamysql
Kiry Meas
Kiry Meas

Reputation: 1242

how can I execute a multi sql line in Java?

These three line of code are have to be execute at the same time.

  SET @num := 0;

        UPDATE your_table SET id = @num := (@num+1);

        ALTER TABLE tableName AUTO_INCREMENT = 1;
    ********************************************************
    Statement s1 = DataCon.getDataCon().createStatement();
                            s1.execute("SET @num := 0; UPDATE tblstaff SET staffid = @num := (@num+1); ALTER TABLE tblstaff AUTO_INCREMENT = 1");
                            s1.close();

Upvotes: 0

Views: 72

Answers (2)

Kiry Meas
Kiry Meas

Reputation: 1242

Try this! I Found it very useful if you wanna keep data in numerical order

Statement stm = DataCon.getDataCon().createStatement(); 
String stm1 = "SET @num := 0"; 
String stm2 = "UPDATE tblstaff SET staffid = @num := (@num+1)"; 
String stm3 = "ALTER TABLE tblstaff AUTO_INCREMENT = 1"; 
stm.addBatch(stm1); 
stm.addBatch(stm2); 
stm.addBatch(stm3); 
stm.executeBatch(); 
stm.close();

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269823

Interestingly, you don't need to. Because you are not using order by, you can use:

UPDATE your_table CROSS JOIN (SELECT @num := 0) params
    SET id = @num := (@num+1);

Having said that, ORDER BY is often desirable in this situation.

Upvotes: 0

Related Questions