What's wrong with this method of dynamically updating multiple rows?

Question

exports.createTaskDataForNewDay = async function(values) {
  try {
    console.log("values", JSON.stringify(values))
    let pool = await CreatePool() //[timestamp , requiredTimes , reward , difficulty ,taskId , uid , csn]
    let query = "update userTaskData set timestamp = ?,requiredTimes=?,timesCompleted=0,reward=?,difficulty=?,state=1,taskId=?,replacedF=0,replacedC=0 where uid =? and suitCase = ?"
    let resp = await pool.query(query, [values])
    if (resp.changedRows > 0) {
      return resp
    } else return {
      code: 400,
      mesage: "Could not insert data ! please try again or check syntax"
    }
  } catch (error) {
    console.error(error)
    return {
      code: 500,
      message: error.message
    }
  }
}

The "value" passed to the function is an array of arrays. Each element holds placeholder data for a different row that I want to update. But I get an error which is Parse Error - There is something wrong with the syntax and the logged query is -

sql: `update userTaskData set timestamp = (1686124176992, 1, '{\"t\":\"c\",\"v\":1000}', 1, 't1', '21GGZzSudOdUjKXcbVQHtFtTK772', 1), (1686124176992, 3, '{\"t\":\"g\",\"v\":10}', 1, 't9', '21GGZzSudOdUjKXcbVQHtFtTK772', 1), (1686124176992, 5, '{\"t\":\"c\",\"v\":4000}', 2, 't17', '21GGZzSudOdUjKXcbVQHtFtTK772', 1), (1686124176992, 3, '{\"t\":\"c\",\"v\":1000}', 3, 't21', '21GGZzSudOdUjKXcbVQHtFtTK772', 1),requiredTimes=?,timesCompleted=0,reward=?,difficulty=?,state=1,taskId=?,replacedF=0,replacedC=0 where uid =? and suitCase = ?

It places all elements in the first placeholder. It works fine for insert queries. Please tell me what I'm doing wrong.

Answer 1

Maybe "value" is already an array and doesn't need to be wrapped in another array. You can pass it directly as follows:

Let resp = wait pool.query(query, value);

Answer 2

The

mysqljs/mysql module does not support batch logging via objects or arrays for the UPDATE method with which you are familiar INSERT The method is the same. What you're imagining isn't even a native feature of MySQL and at most it can be achieved by switching CASE if you want to use the actual UPDATE method.

This leaves you with two options:

Option 1

As long as your table and the data provided for the update contain unique keys, you can use INSERT INTO table_name SET ?About duplicate key updates...

Option 2

If you don't have unique keyed fields and corresponding values ​​in your query for a given table, you have to build the query by iterating over the entries so that you end up with a collection of strings containing as many

updates as you want You will have the query -or- by building a query that utilizes the CASE condition.

You can see some examples here:

How to create a dynamic insert statement based on the POST body