Generating a dynamic SQL Update statement using an array of objects

Question

Generating a dynamic SQL Update statement using an array of objects

I am currently working with an array of objects in order to create an Update SQL statement. Here is the array I am using:

let dataUpdate = [
  {
     "field1":123,
     "field2":"BMW",
     "field3":"blue"
  }
]

This is my attempted approach:

let query: string = `UPDATE dataset.table SET` + dataTest.forEach((item:any) =>
  Object.entries(item).map(([key,value]) => {
    `${key} = '${value}'`
  }).join(',')
)

After running this code, I end up with:

UPDATE dataset.table SETundefined

What I actually want to achieve is:

UPDATE dataset.table SET field2="BMW", field3="blue" WHERE field1=123

javascript sql arrays typescript sql-update

Answer 1

Answer №1

It is essential to distinguish between the criteria and the update in your code structure. I have implemented a function named updateQuery, which generates the required query as shown below.

const quote = (val) => typeof val === 'string' ? `"${val}"` : val;

const updateQuery = (table, criteria, update) =>
  `UPDATE ${table} SET ${Object.entries(update)
    .map(([field, value]) => `${field}=${quote(value)}`)
    .join(', ')} WHERE ${Object.entries(criteria)
    .map(([field, value]) => `${field}=${quote(value)}`)
    .join(' AND ')}`;

const expected = 'UPDATE dataset.table SET field2="BMW", field3="blue" WHERE field1=123';

const actual = updateQuery(
  'dataset.table',
  { 'field1': 123 },
  { 'field2': 'BMW', 'field3': 'blue' }
);

console.log(expected === actual);

Answer 2

It is essential to distinguish between the criteria and the update in your code structure. I have implemented a function named updateQuery, which generates the required query as shown below.

const quote = (val) => typeof val === 'string' ? `"${val}"` : val;

const updateQuery = (table, criteria, update) =>
  `UPDATE ${table} SET ${Object.entries(update)
    .map(([field, value]) => `${field}=${quote(value)}`)
    .join(', ')} WHERE ${Object.entries(criteria)
    .map(([field, value]) => `${field}=${quote(value)}`)
    .join(' AND ')}`;

const expected = 'UPDATE dataset.table SET field2="BMW", field3="blue" WHERE field1=123';

const actual = updateQuery(
  'dataset.table',
  { 'field1': 123 },
  { 'field2': 'BMW', 'field3': 'blue' }
);

console.log(expected === actual);

Generating a dynamic SQL Update statement using an array of objects

Answer №1

Similar questions

New and Improved React Material Collapse with onClick Event [Answer Refreshed]

Search through array elements that are nested deeply

Insert a hyperlink button using InnerHtml

Unable to dynamically add an element to a nested array in real-time

When you start scrolling down, the JavaScript menu will cleverly disappear, only to re

Angular data table is currently displaying an empty dataset with no information available

Exploring the World of jQuery Caching and Navigating Through Objects

Exporting Textures with Custom Offsets to GLTF from Three.js Scene

Avoiding the repetition of CSS animations during Gatsby page hydration

Unable to clear form using `$setPristine` command

Managing connection pooling in Node.js Express with Oracle (ORA-24418 error: Unable to open additional sessions)

An issue arises when trying to utilize meta tags in Nuxtjs while incorporating TypeScript into the

The dataset controller in Chart.js returns 'null' when the chart is rendered

Creating an array in a Java applet by entering numbers into a textfield

Use the clientID property of the controlname element within JavaScript to access the element using the getElementById method with only

Promise of a repeating sequence of serial calls

Invoke a method in an Angular 2 component using an HTML event

Basic javascript doesn't trigger

Having difficulty accessing information from the parent scope

What is the best way to customize fonts for PDFMake in Angular projects?