'Can I use knex.js onConflict().merge() to add to an integer column's value?
I have a MySQL table something like this:
CREATE TABLE `some_table` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`some_other_id` INT(11) NOT NULL,
`some_date` DATE NOT NULL,
`some_total` INT(11) NOT NULL DEFAULT 0,
`UNIQUE KEY `uq_some_key` (`some_other_id`, `period_date`)
)
I can "upsert" row values, adding to the some_total column, in MySQL with this:
INSERT INTO `some_table` (`some_other_id`, `some_date`, `some_total`)
VALUES (1, '2022-01-01', 1)
ON DUPLICATE KEY UPDATE
`some_total` = `some_total` + VALUES(`some_total`);
This statement inserts a row if the unique constraint on some_other_id and some_date is not hit, or updates the row if the constraint is hit.
Is it possible to use Knex.js to do this? I see that it has onConflict() and merge() functionality to emulate ON DUPLICATE KEY UPDATE, but I don't know if it will infer the unique constraint for the onConflict(). And I can't figure out the syntax. When I try this, some_total is undefined:
knex('some_table')
.insert({
some_other_id: 1,
some_date: '2022-01-01',
some_total: 42
})
.onConflict()
.merge({
some_total: some_total + 42
});
Is this possible, or do I have to use a raw() query?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
