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?
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