KaPaHgaIII KaPaHgaIII - 3 months ago 16
SQL Question

H2 - update each row with its index number (0,1,2,3,..)

I have a table in me H2 db, which stores some ordered list:

CREATE TABLE IF NOT EXISTS items
(
f_id INTEGER NOT NULL PRIMARY KEY,
f_name VARCHAR(255) NOT NULL,
f_index INTEGER NOT NULL UNIQUE
)


|f_id|f_name|f_index|
|1 |A |1 |
|2 |C |3 |
|3 |B |4 |
|4 |D |2 |


When I remove item 'D', I need to update C's index to 2 and B's index to 3.

And I am going crazy when triyng to do this. I don't want to simply

UPDATE items SET f_index = f_index-1 WHERE f_index > ?


because I am afraid my table can contain gaps (caused by unknown reasons), so I look for a something like

SqlRowSet rowSet = jdbcTemplate.queryForRowSet("SELECT f_name FROM items ORDER BY f_index");
int i = 0;
while (rowSet.next()) {
String name = rowSet.getString("f_name");
jdbcTemplate.update("UPDATE items SET f_index = ? WHERE f_name= ?", i++, name);
}


but more elegant.

Is there any way to achieve my needs with H2?

Answer

If you just want to use h2 then you can use the following statement

merge into items (f_id, f_index) key(f_id) select f_id, rownum()  from items order by f_index
Comments