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

1 Answer 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 });
});