SQL - Set column value as difference of values from different rows matched by common id

Question

I have a table which has the following columns:

 // zip // date_qtr // vac_ratio // vac_change // 
100     12_2016     0.56         NULL
101     12_2016     0.44         NULL
100      9_2016     0.38         NULL
 .          .        .
 .          .        .
101      9_2016     0.12         NULL

I want to find the change in vac_ratio(against previous quarters vac_ratio) for each quarter for each zip. So basically for each row in table with date_qtr => 12_2016

vac_change (new column) = (vac_ratio(12_2016 and zip=z1) - vac_ratio (9_2016 and zip=z1))/ vac_ratio (9_2016 and zip=z1)

I am able to get the vac_change value using a JOIN but i am finding it difficult to update the column vac_change.

UPDATE  h0 SET h0.vac_change= a0.vac_change FROM hud_vacancies h0 JOIN 
(SELECT h1.zip,h1.date_qtr,((h1.vac_ratio - h2.vac_ratio)/h2.vac_ratio ) AS vac_change 
FROM hud_vacancies h1 JOIN hud_vacancies h2 ON h1.zip = h2.zip 
WHERE h1.date_qtr LIKE '12_2016' and h2.zip LIKE '9_2016')a0 ON h0.zip = a0.zip WHERE h0.date_qtr LIKE '12_2016'

I am getting an SQL syntax error, but I dont understand what is the issue.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM hud_vacancies h0 JOIN (SELECT h1.zip,h1.date_qtr,((h1.vac_ratio - h2.vac_r' at line 1

I based my sql script similar to the solution provided here. I hope the question is clear, please let me know if any more information is required.

EDIT: As per the input given by @Gordon , I tried a variation of the script without the FROM operator. It does not give me a syntax error now, but neither does it SET the column.The columns are still NULL and no row was affected. Here is the modified code.

UPDATE  hud_vacancies h0 
  JOIN 
     ( SELECT h1.zip
            , h1.date_qtr
            , ((h1.vac_ratio - h2.vac_ratio)/h2.vac_ratio ) vac_change 
         FROM hud_vacancies h1 
         JOIN hud_vacancies h2 
           ON h1.zip = h2.zip 
        WHERE h1.date_qtr LIKE '12_2016' 
          and h2.zip LIKE '9_2016'
     ) a0 
    ON h0.zip = a0.zip 
   SET h0.vac_change = a0.vac_change 
 WHERE h0.date_qtr LIKE '12_2016'

PS: I apologize as I know the title mentions difference while I am trying to calculate the rate of change, but I tried to maintain a generic title and there is a small difference in implementation of the calculation of difference and rate of change.

MySQL does not support the `FROM` clause in an `UPDATE` statement. — Gordon Linoff, Apr 24 '17 at 16:50
See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Apr 24 '17 at 17:14
You used `SQL Server` syntax. I think in `MySQL`, you `JOIN` before you `SET`. — Eric, Apr 24 '17 at 17:16
Incidentally, I can see no difference between `LIKE '12_2016'` and `= '12_2016'`. — Strawberry, Apr 24 '17 at 17:18

score 0 · Answer 1 · answered Apr 26 '17 at 17:52

There was a small mistake in my query, which caused the issue of not updating any rows. I was applying the where clause h2.zip LIKE '9_2016' instead of h2.date_qtr LIKE '9_2016'.

Thank You Gordon, Strawberry and Eric for your inputs.

Here is the corrected working query.

UPDATE  hud_vacancies h0 JOIN 
(SELECT h1.zip,h1.date_qtr,(h1.vac_ratio - h2.vac_ratio) AS vac_change 
FROM hud_vacancies h1 JOIN hud_vacancies h2 ON h1.zip = h2.zip 
WHERE h1.date_qtr LIKE '12_2016' and h2.date_qtr LIKE '9_2016') 
a0 ON h0.zip = a0.zip 
SET h0.vac_change= a0.vac_change WHERE h0.date_qtr LIKE '12_2016'

SQL - Set column value as difference of values from different rows matched by common id

1 Answers1