How to get a list of all updated records in knex using Raw / SQL Server

async updatePerson(critera, transaction) {
    return await this.knex.raw(`
    update person
          SET name = :name_new
          WHERE name = :name_old
`, critera)
      .transacting(transaction);
}

Critera is object of following

{name_new: 'test person', name_old: 'test person2'}

transaction is knex.transaction: https://knexjs.org/#Builder-transacting

I would like to return the updated records and/or updated total in SQL count. So I can let user know about the result of SQL run

but with using above SQL query in RAW with SQL Server.

To get inserted value, I use following which works fine. Not sure how this can be achieve in case of update SQL:

async addPerson(person, transaction) {
    return await this.knex.raw(`insert into person(
        person_id
       ,name

        )  output inserted.[person_id]
 values(
        NEWID()
       ,:name
          )`, person)
      .transacting(transaction);
  }

person is object {name: 'test person 123'}

Ref: https://knexjs.org/#Raw-Bindings

Solution

You also can use OUTPUT clause with UPDATE:

UPDATE person
SET name = :name_new
OUTPUT deleted.name old_name, inserted.name new_name, inserted.person_id
WHERE name = :name_old

Test:

CREATE TABLE #person(person_id int,name varchar(10))

INSERT #person(person_id,name)VALUES(1,'aaa'),(2,'bbb'),(3,'bbb')

-- returns 2 rows
UPDATE #person
SET name = 'ccc'
OUTPUT deleted.name old_name, inserted.name new_name, inserted.person_id
WHERE name = 'bbb'

-- returns 0 rows
UPDATE #person
SET name = 'yyy'
OUTPUT deleted.name old_name, inserted.name new_name, inserted.person_id
WHERE name = 'zzz'

DROP TABLE #person

Ref: https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql