# Calculate result with previous record and update in the new record

2

I want to take a tour of the 'data' table and in each of the registers I need to take the Mileage from the previous record, then subtract New Mileage - Old Mileage and divide it between Liters to get Efficiency and assign it to the record, but I do not know a lot of database, how could you read each record and update?

in advance thank you very much! I'll capture you.

asked by Ed Brennenburg 22.09.2018 в 02:42
source

2

Try this:

To go through the data table and get the old Mileage and subtract it with the new one and also with the division of said subtraction:

``````SELECT mileage,
mileage_previo,
(mileage - mileage_previo) as resta,
(mileage - mileage_previo) / liters  as efficiency
FROM(
SELECT mileage,
CASE
WHEN (SELECT d2.id FROM data d2 WHERE d2.id < d.id LIMIT 1) IS NULL THEN 0
ELSE (SELECT d2.id FROM data d2 WHERE d2.id < d.id LIMIT 1)
END mileage_previo,
liters
FROM data d
) t
``````

Now to insert in the table data based on this query you just have to do:

`````` INSERT INTO data (mileage, liters , ..., coln)
SELECT mileage,
mileage_previo,
(mileage - mileage_previo) as resta,
(mileage - mileage_previo) / liters  as efficiency
FROM(
SELECT mileage,
CASE
WHEN (SELECT d2.id FROM data d2 WHERE d2.id < d.id LIMIT 1) IS NULL THEN 0
ELSE (SELECT d2.id FROM data d2 WHERE d2.id < d.id LIMIT 1)
END mileage_previo,
liters
FROM data d
) t
``````

or update what I think is what you want:

``````UPDATE data
SET efficiency = (SELECT
(mileage - mileage_previo) / liters  as efficiency
FROM(
SELECT mileage,
CASE
WHEN (SELECT d2.id FROM data d2 WHERE d2.id < d.id LIMIT 1) IS NULL THEN 0
ELSE (SELECT d2.id FROM data d2 WHERE d2.id < d.id LIMIT 1)
END mileage_previo,
liters
FROM data d
) t
)
WHERE id IN (SELECT id FROM data)
``````