I want to update one single row in database, I tried to put the .limit(1), doesn't seem to work with Postgres.

What would you do in this situation?

list.map(async (i) => {
            await kx(table.products)
                .update({ series: i, machine: i.machine })
                .where({
                    series: '',
                    machine: '',
                    purchaseDate: '',
                })
                .limit(1)
        })

I want to update one single row in database, I tried to put the .limit(1), doesn't seem to work with Postgres.

What would you do in this situation?

list.map(async (i) => {
            await kx(table.products)
                .update({ series: i, machine: i.machine })
                .where({
                    series: '',
                    machine: '',
                    purchaseDate: '',
                })
                .limit(1)
        })

• knex.js

Share Improve this question Follow edited Mar 28 at 4:37 angry kiwi asked Mar 27 at 16:54 angry kiwiangry kiwi 11.5k2828 gold badges123123 silver badges167167 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

In PostgreSQL, the .limit(1) is not valid for an UPDATE query because the SQL standard does not allow limiting the number of rows directly in an UPDATE statement.

Instead of using .limit(1), revise the WHERE clause, ensuring that it matches a single, specific row e.g. using id.

list.map(async (i) => {
  await kx(table.claim)
    .update({ series: i, machine: i.machine })
    .where({
      id: i.id 
    });
});

If there is no unique column and your query can match multiple rows, you can use a subquery with the ctid (a system column specific to PostgreSQL). The ctid is unique to each row in the table and can help to target one specific row.

list.map(async (i) => {
  await kx(table.claim)
    .whereRaw('(series = ? AND machine = ? AND purchaseDate = ?) AND ctid IN (SELECT ctid FROM ?? WHERE series = ? AND machine = ? AND purchaseDate = ? LIMIT 1)', 
      ['', '', '', table.claim, '', '', ''])
    .update({ series: i, machine: i.machine });
});