knex: Update operations do not support joins

Environment

Knex version: 0.15.2

Feature request

  await knex('table')
    .innerJoin('table2', function() {
      this.on(
        `table.id`,
        `table2.id`
      ).andOn(`table.field`, 'value')
    })
    .update({
      table.field2: 'newValue,
    })

This is going to execute without an error, but entire join part will be ignored and all entries will be silently updated.

https://github.com/tgriesser/knex/issues/1534 suggests either using raw or subquery, but neither of these solutions is particularly elegant, and API-wise there is no reason why this shouldn’t work, so I would consider this a bug.

About this issue

Original URL
State: open
Created 6 years ago
Reactions: 13
Comments: 16 (8 by maintainers)

Most upvoted comments

The worst part is that the query fails silently, if using Postgres it should at least throw an error.

jpike88 on Jan 2, 2023

Not bug, but a missing feature. This has been discussed a lot during history and is one of the most wanted features in my list. I don’t know if it would be hard to implement or not. I would suggest reading builder / compilation code of update operation to see if join parameters are even collected to builder for update operations.

Also to start with one needs to figure out how those joins with update work with different databases (postgresql, mysql, mssql, oracledb, sqlite) and see which databases even can be supported.

Pretty old issue about it https://github.com/tgriesser/knex/issues/557 This is how it should not be implemented https://github.com/tgriesser/knex/pull/2578 Somewhat related for delerte https://github.com/tgriesser/knex/issues/873

elhigu on Sep 3, 2018