Updating row by row after have to sort a set of data mysql

Question

I I have a table table1 like this in MySQL Db without primary key:

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| value   | varchar(25) | YES  |     | NULL    |       |
| my_date | date        | YES  | MUL | NULL    |       |
| my_time | time        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

Is it possible, after to sort the table by my_date and my_time value, update id value start from a given value ?

For example from this set of value

+------+------------+----------+
| id   | my_date    | my_time  |
+------+------------+----------+
|    0 | 2018-03-01 | 09:02:00 |
|    0 | 2018-03-01 | 09:01:00 |
|    0 | 2018-03-01 | 09:00:00 |
+------+------------+----------+

I want to obtain

+------+------------+----------+
| id   | my_date    | my_time  |
+------+------------+----------+
|  100 | 2018-03-01 | 09:00:00 |
|  101 | 2018-03-01 | 09:01:00 |
|  102 | 2018-03-01 | 09:02:00 |
+------+------------+----------+

I have tried something like this without success:

try {
    int idFromToStart = 100; 
    String query = "SELECT IFNULL(id, 0) FROM table1 WHERE my_date > '2018-02-28' order by my_date, my_time";
    preparedStatement =con.prepareStatement(query, ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_UPDATABLE);            
    ResultSet rs = preparedStatement.executeQuery(query);
    System.out.println("query " + query);            
    if (rs.next()) {
        rs.updateLong(1, idFromToStart);
        System.out.println("rs.getLong(1)) "+rs.getLong(1)+ " idFromToStart "+idFromToStart);
        idFromToStart++;
    }
} catch (SQLException ex) {
    System.out.println("Error db " + ex.getMessage() + " " + ex.toString());
}

try cast time column to int and then sort https://stackoverflow.com/questions/11808573/sql-order-string-as-number — kallosz, Aug 21 '18 at 11:05

Ildar Akhmetov · Accepted Answer · 2018-08-21T11:08:25.660

1

SET @rownum = 99;
UPDATE tbl1
SET tbl1.id = @rownum:=@rownum+1
ORDER BY my_time

Here is an SQLFiddle: http://sqlfiddle.com/#!9/34699f/1

edited Aug 21 '18 at 11:08

answered Aug 21 '18 at 11:04

Ildar Akhmetov

1,331
13
22

@famedoro Come on, this SQL code should solve your problem. Wrapping up a simple `SET` and `UPDATE` in Java doesn't sound like a serious issue. – Ildar Akhmetov Aug 21 '18 at 11:10
I'm sorry, so is it possible to use variables like @rownum in a `set` and `update` in java? Now I try – famedoro Aug 21 '18 at 11:13
@famedoro Yeah, it should be possible. For example, take a look here: https://stackoverflow.com/questions/22949908/using-a-mysql-variable-in-java-for-a-ranking-query – Ildar Akhmetov Aug 21 '18 at 11:15
@famedoro Glad it helped! – Ildar Akhmetov Aug 21 '18 at 11:43

score 0 · Answer 2 · answered Aug 21 '18 at 11:06

0

If you don't break anything by deleting rows in that table, move it all into a temporary table, truncate the current one, set id to auto increment and do the following:

INSERT INTO table1 (`value`, my_date, my_time)
SELECT `value`, my_date, my_time
FROM myTmpTable
ORDER BY my_date, my_time ASC;

answered Aug 21 '18 at 11:06

DataVader

740
1
5
19

I prefer do not use a temp table, because I have a lot of data – famedoro Aug 21 '18 at 11:08
Use a temporary table of the type "memory" or "MyISAM" to increase speed. If both are not an option, is any combination of column values unique? If yes, it would be possible via a window-function. If not, you only have the option of using a temporary table or a really slow procedure with loops. – DataVader Aug 21 '18 at 11:19

Updating row by row after have to sort a set of data mysql

2 Answers2