{"id":3932,"date":"2024-07-15T15:22:06","date_gmt":"2024-07-15T08:22:06","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=3932"},"modified":"2024-07-22T07:50:44","modified_gmt":"2024-07-22T00:50:44","slug":"nodejs-sql-server-update","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/nodejs-sql-server\/nodejs-sql-server-update\/","title":{"rendered":"Node.js SQL Server: Update"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to update data in SQL Server from Node.js applications.<\/p>\n\n\n\n

This tutorial begins where\u00a0the Performing a Bulk Insert from Node.js tutorial<\/a>\u00a0left off.<\/p>\n\n\n\n

We’ll show you how to update the author’s first name in the Authors<\/code> table.<\/p>\n\n\n\n

Updating data in a table #<\/a><\/h2>\n\n\n\n

Step 1. Create a new file called update.js<\/code> in the project directory.<\/p>\n\n\n\n

Step 2. Define a new function updateAuthor<\/code> in the update.js<\/code> file to update the author’s first name:<\/p>\n\n\n

import<\/span> sql from<\/span> 'mssql'<\/span>;\nimport<\/span> { config } from<\/span> '.\/config.js'<\/span>;\n\nexport<\/span> const<\/span> updateAuthor = async<\/span> (id, firstName) => {\n  try<\/span> {\n    \/\/ Connect to the database<\/span>\n    await<\/span> sql.connect(config);\n\n    \/\/ Update the First Name<\/span>\n    const<\/span> result =\n      await<\/span> sql.query`UPDATE Authors SET FirstName =${firstName}<\/span> WHERE AuthorID=${id}<\/span>`<\/span>;\n\n    console<\/span>.dir(result);\n  } catch<\/span> (err) {\n    console<\/span>.error(err);\n  }\n};<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, import the sql<\/code> object from the mssql<\/code> module and the config<\/code> object from the config.js<\/code> module respectively:<\/p>\n\n\n
import<\/span> sql from<\/span> 'mssql'<\/span>;\nimport<\/span> { config } from<\/span> '.\/config.js'<\/span>;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that the config<\/code> object contains all the database parameters including server, database name, username, and password.<\/p>\n\n\n\n
Second, define an updateAuthor<\/code> function that updates the first name of an author specified by an id:<\/p>\n\n\n
export<\/span> const<\/span> updateAuthor = async<\/span> (id, firstName) => {<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, connect to the SQL Server using the connect()<\/code> method of the sql<\/code> object:<\/p>\n\n\n
await<\/span> sql.connect(config);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, execute an UPDATE<\/code> statement to change the name of the author specified by the author id to the new one:<\/p>\n\n\n
const<\/span> result =\n      await<\/span> sql.query`UPDATE Authors SET FirstName =${firstName}<\/span> WHERE AuthorID=${id}<\/span>`<\/span>;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, display the result if the query is executed successfully: <\/p>\n\n\n
console<\/span>.dir(result);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The result object contains the number of rows updated. To access it, you use the rowsAffected<\/code> property:<\/p>\n\n\n
result.rowsAffected<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
It is an array of numbers indicating the row affected by statements. To access the rows affected by the first statement, you can access the first element of the result.rowsAffected<\/code> array:<\/p>\n\n\n
if<\/span> (result.rowsAffected.length >= 1<\/span>) {\n  console<\/span>.log(`Row(s) affected: ${result.rowsAffected[0<\/span>]}<\/span>`<\/span>);\n}<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Import the updateAuthor<\/code> from the update.js<\/code> module and call it to update the first name of the author with id 14<\/code> to 'Joanne Kathleen'<\/code>:<\/p>\n\n\n
import<\/span> { updateAuthor } from<\/span> '.\/update.js'<\/span>;\n\nawait<\/span> updateAuthor(14<\/span>, 'Joanne Kathleen'<\/span>);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 4. Open the terminal and run the following command to run the index.js<\/code> file:<\/p>\n\n\n
npm start<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
If you see the following output, you have successfully updated the row:<\/p>\n\n\n
{\n  recordsets<\/span>: [],\n  recordset<\/span>: undefined<\/span>,\n  output<\/span>: {},\n  rowsAffected<\/span>: [ 1<\/span> ]\n}<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that the rowsAffected<\/code> contains the number 1, meaning that one row has been updated successfully.<\/p>\n\n\n\n
Here’s the detailed information on the properties of the result object:<\/p>\n\n\n\n