CODEWITHSUNDEEP
javamysqlsql
Alon Shmiel
Alon Shmiel

Reputation: 7121

Java: updating the MySQL table's id

This is my delete class that gets id as an input.

I succeeded to delete the row(for example row contained id '3' from my database) and now I want to update all the id-s. for example: my rows were:

1 a5

2 f3

(3 t1 was deleted)

4 r2

so now the result should be updated:

1 a5

2 f3

3 r2

String delete = "DELETE from authors WHERE id = ?";

          Class.forName("com.mysql.jdbc.Driver");
          Connection con = DriverManager.getConnection(url, "root", "easttg");
          PreparedStatement ps = con.prepareStatement(delete);

          //updating the id
          String sql = "Select id from authors";
          PreparedStatement ps2 = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

          ps.setInt(1, id);
          ps.executeUpdate();
          con.close();

Upvotes: 0

Views: 509

Answers (1)

JB Nizet
JB Nizet

Reputation: 691755

To update some row, you must use an update statement. Not a select statement:

update authors set id = ? where id = ?

That said: IDs are just technical identifiers for rows. They shouldn't be modified generally. And the reason why purely technical IDs are used is that they never need to be modified, which is a good thing because there might be dozens of other tables having foreign keys containing this same ID. The ID might also be part of some URL used to display the row in a web site, that many people could have bookmarked. The ID is... the identity of the row. You don't change an identity. If you do it, all kinds of problems occur, exactly as if you changed your own name.

Upvotes: 2

Related Questions