How do I perform matematical operations in an update

[joviwap]

I want to create de following query: UPDATE products SET updated = now(), remaining = remaining - 1 WHERE id in ($1, $2, ...)

I have tied this:

sql.update('products ')`
  .set({updated:'now()', remaining : 'remaining  - 1'})
  .where($in('id', ...providersIds))
  .toParams();

but it does not work because it consideres 'remaining - 1' as an string. How can I specify this values is an operation?

[prust]

@joviwap: When you want to include literal SQL in a query like this, simply wrap it in the sql() method, like this: {updated: sql('now()'), remaining: sql('remaining - 1')}. Here's a more complete example:

var providerIds = [36, 42, 27];

sql.update('products ')
  .set({updated: sql('now()'), remaining : sql('remaining  - 1')})
  .where($in('id', ...providerIds))
  .toParams();

// {
//    text: "UPDATE products  SET updated = now(), remaining = remaining  - 1 WHERE id IN ($1, $2, $3)",
//    values: [36, 42, 27]
//  }

[prust]

@joviwap: For reference, here is an excerpt from the docs for the sql() function:

sql(str[, values])

The SQL Bricks namespace (saved to the local variable sql in these docs) can be called as a function to insert SQL into SQL Bricks somewhere that a value is expected (the right-hand side of WHERE criteria, or insert()/update() values):

select('*').from('person').where({'billing_addr_id': sql('mailing_addr_id')})
// SELECT * FROM person WHERE billing_addr_id = mailing_addr_id