How do I perform matematical operations in an update
joviwap opened this issue · 2 comments
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?
@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]
// }
@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