26

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.

A2MetalCore
  • 1,621
  • 4
  • 25
  • 49

1 Answers1

57

Here are 2 different ways

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

or

knex('item').update({
  qtyonhand: knex.raw('?? + 1', ['qtyonhand'])
}).where('rowid',8)
Mikael Lepistö
  • 18,909
  • 3
  • 68
  • 70
  • hello mikael, i tried .increment, but found the amount to add should be also specified. i.e. .increment('qtyonhand', 1) will do – alex Apr 22 '22 at 10:13
  • 1
    Hi @alex ! Knex does not require that second parameter. I wrote a small test to demonstrate it https://runkit.com/embed/zqlhh6jf1jqn However TypeScript typings might force it. – Mikael Lepistö Apr 25 '22 at 08:54
  • i believe you @mikael. my comment was not based on knowledge, but on my trial after knex failed for me. – alex Apr 25 '22 at 18:16