Knex.js - How To Update a Field With An Expression

mysql sql-update knex.js
ASA2 picture ASA2 · Feb 13, 2017 · Viewed 9.2k times · Source

How do we get Knex to create the following SQL statement:

UPDATE item SET qtyonhand = qtyonhand + 1 WHERE rowid = 8

We're currently using the following code:

knex('item')
    .transacting(trx)
    .update({qtyonhand: 10})
    .where('rowid', 8)

However, in order for our inventory application to work in a multi-user environment we need the qtyonhand value to add or subtract with what's actually in the database at that moment rather than passing a value that may be stale by the time the update statement is executed.

Answer

Mikael Lepistö picture Mikael Lepistö · Feb 16, 2017

Here are 2 different ways

knex('item').increment('qtyonhand').where('rowid',8)

or

knex('item').update({
  qtyonhand: knex.raw('?? + 1', ['qtyonhand'])
}).where('rowid',8)

Related questions

MySQL error code: 1175 during UPDATE in MySQL Workbench

I'm trying to update the column visited to give it the value 1. I use MySQL workbench, and I'm writing the statement in the SQL editor from inside the workbench. I'm writing the following command: UPDATE tablename SET columnname=1; It gives …

Read More
MySQL - UPDATE query based on SELECT Query

I need to check (from the same table) if there is an association between two events based on date-time. One set of data will contain the ending date-time of certain events and the other set of data will contain the …

Read More
How to update column with null value

I am using mysql and need to update a column with a null value. I have tried this many different ways and the best I have gotten is an empty string. Is there a special syntax to do this?

Read More