annual change of values with subquery

Question

This query gives a 1093 error:

UPDATE values_table as outer_select 
    SET annual_change = sample_value - (
        SELECT sample_value FROM values_table 
        WHERE date_sampled = DATE_SUB(outer_select.date_sampled, INTERVAL 1 YEAR)
    );

I'm trying to set annual_change for every row equal to the current row's sample_value less last year's sample_value.

The data doesn't go back to the beginning time, so how can the lack of historical values also be handled?

Answer 1

Try this

UPDATE values_table as a
join  values_table as b 
ON b.date_sampled = DATE_SUB(a.date_sampled, INTERVAL 1 YEAR)
set a.annual_change = a.sample_value - b.sample_value