I have a table Chron containing the following columns:
ID | timestamp | ...
Some timestamps have been erroneously changed to a specific date D. I want to change them to the timestamp of the nearest rows (in terms of ID) which have not been changed. My query is:
UPDATE Chron as C1 SET C1.timestamp = ( SELECT C2.timestamp FROM Chron as C2 WHERE C2.timestamp != D AND NOT EXISTS ( SELECT * FROM Chron as C3 WHERE ABS(C2.id - C1.id) > ABS(C3.id - C1.id) ) ) WHERE C1.timestamp = D
The problem is that my table contains more than 300K rows and this query would take a lot of time to run. How can I optimize it? I know that it is not necessary to compare a given row C1 with the whole table, but just the neighbourhood is sufficient, but the optimal size of the neighbourhood is not always the same.