Create dynamic SQL Update from an array of objects

Question

I have an array of objects with which I am trying to create an Update SQL.

This is my array:

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

This is what I am trying to do:

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

This is what I get:

UPDATE dataset.table SETundefined

This is what I need:

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

Answer 1

You need to separate the criteria from the update. I created a function called updateQuery, that constructs the desired query, 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);