Calculate result with previous record and update in the new record

Question

Calculate result with previous record and update in the new record

Navigation

#1 by (2 votes)

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.

mysql

asked by Ed Brennenburg 22.09.2018 в 02:42

source

1 answer

Error in xsd dian colombia Why does the same date appear on all Users?

score 2 · Accepted Answer

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)