admin 管理员组

文章数量: 1086019

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 edited Mar 28 at 4:37 angry kiwi asked Mar 27 at 16:54 angry kiwiangry kiwi 11.5k28 gold badges123 silver badges167 bronze badges
Add a comment  | 

1 Answer 1

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

本文标签： knexjsHow to update one single rowStack Overflow

版权声明：本文标题：knex.js - How to update one single row? - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.roclinux.cn/p/1744078341a2529769.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。