0

I have two tables that are something like this..

table2015                                table2016
=======+===========+========             =======+===========+========
id     |remains    |days_off             id     |remains    |days_off 
=======+===========+========             =======+===========+========   
1      |4          |12                    1     |12         |12
2      |-2         |12                    2     |12         |12
3      |3          |25                    3     |25         |25

How can I edit days_off on table2016 only if the remains on table2015 are less than 0? This is the result needed:

table2015                                table2016
id     |remains    |days_off             id     |remains    |days_off 
=======+===========+========             =======+===========+========   
1      |4          |12                    1     |12         |12
2      |-2         |12                    2     |12         |10
3      |0          |25                    3     |25         |25

I've tried this query but it gave me an error on line 5

UPDATE 
    table2016
SET 
    table2016.days_off = table2016.days_off + table2015.remains
FROM 
    table2016 
JOIN 
    table2015
ON 
    table2016.id = table2015.id
AND 
    table2015.remains < 0
CerebralCortexan
  • 299
  • 2
  • 17
Darjeeling
  • 959
  • 5
  • 19
  • 40
  • 1
    Your join is correct, but you have the syntax wrong for MySQL. It goes `UPDATE table JOIN othertable ON condition SET column = value`. So remove `FROM` and move the table join right after `UPDATE`. The `SET` comes last. Examples here... http://dev.mysql.com/doc/refman/5.7/en/update.html – Michael Berkowski Dec 07 '15 at 03:01
  • Your error is almost identical to that in [this question](http://stackoverflow.com/questions/8331687/mysql-update-a-joined-table) – Michael Berkowski Dec 07 '15 at 03:02
  • oh yeah, you're right, silly me, thanks – Darjeeling Dec 07 '15 at 03:09
  • If you have troubles, use this `update table2016 t2, table2015 t1 set t2.days_off = t2.days_off + t1.remains where t1.id = t2.id and t1.remains < 0` – zedfoxus Dec 07 '15 at 03:10

0 Answers0