How to update some rows in database table while processing data from that same table?

I have to write an update statement to update bunch of values in database table.

First a bit of an intro. Our client is from industry of transport and logistics. They provide a service of transport (construction material) between people who need construction material and those who have transportation companies and offer transportation service, truckers.

Driver can get an inquiry, where he needs to do some deliveries and pickups. For example, one inquiry can consist of three tours. He can start tour, pick up material, deliver material, pick up waste (at the same construction site where he drop material), start new subtour, deliver waste, pick up material, start new subtour, deliver material and subtour, and finally end entire tour.

There is also websocket which pings drivers location (mobile client) and writes down longitude and latitude in tour_log table. So, location is tracked every 30 sec.

On the image, you can see how section of that table looks like. That table has over million records.

The web (spring-boot) service, when driver finishes subtour, is not saving to the database for which subtour the driver ended subtour (SUBTOUR_END). I updated that service, but our client, will want some reports for tours and inquiries for some earlier period. I need to perform an update to be able to complete Report functionality.

So, to the main point. I do have a query which is obviously not working. I cannot update a table from which I’m querying data in the same time.

UPDATE tour_log 
SET tour_id = (SELECT tour_id FROM tour_log A 
               WHERE tour_log_id = (SELECT (tour_log_id) 
                                    FROM tour_log B 
                                    WHERE tour_id IS NULL AND log = "SUBTOUR_END" AND A.tour_log_id = B.tour_log_id - 1)) 
WHERE log = "SUBTOUR_END" AND tour_id is null AND inquiry_id = 7519646; -- I'm doing update for single inquiry only

Anyone to give me a hint on how to go about this?

Answer

Test

UPDATE tour_log t1
JOIN tour_log t2 ON t1.truck_id = t2.truck_id 
                AND t1.tour_log_id > t2.tour_log_id
LEFT JOIN tour_log t3 ON t1.truck_id = t3.truck_id 
                AND t1.tour_log_id > t3.tour_log_id
                AND t3.tour_log_id > t2.tour_log_id
SET t1.tour_id = t2.tour_id
WHERE t1.log = 'SUBTOUR_END'
  AND t3.tour_log_id IS NULL

I.e. copy tour_id to the row with 'SUBTOUR_END' from previous adjacent row (t3 is used for to check that there is no row between them).

tour_log_id, not timestamp, is used for ordering because the last one may have duplicates.

Advertisement

Answer