I'm using knex with node.js I want to do a math query (I want to add +1 to already existing values)
I want to do this in knex
UPDATE fares SET fare=fare + 70
This is my query in knex, all I get is 0 in the database
knex('table').update({ fares: 'fares' + 70}).then(function() {});
There are built-in methods to achieve this,
1. Increments
The Increment method will help you to increment column value by some specific value, consider the example from official docs,
knex('accounts').where('userid', '=', 1).increment('balance', 5)
Outputs: update
accountssetbalance=balance+ 5 whereuserid= 1
2. Decrements
knex('accounts').where('userid', '=', 1).decrement('balance', 5)
Outputs: update
accountssetbalance=balance- 5 whereuserid= 1
However, I didn't find methods for multiplication or division. We can do it by the raw query as given below,
knex.raw('update accounts set balance = (balance * ? ) where id = ?', [10 , 1]).then(function(resp) { ... });
Outputs: update accounts set balance = (balance * 10) where id = 1;
The same raw query should work for the division as well.