Question 195386 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – What is wrong with this method for updating multiple rows dynamically?

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 ‘values’ being passed to the function is an array of arrays . each element holding the place holder data for different rows that i want to update .
but i getan error that is parsing error – something wrong with syntax and the query that is logged 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 is placing all the elements in the first place holder. Where as it is working fine for the insert queries .
please tell me what i’m doing wrong.

Tags:

2

Answers


  1. 0

    Perhaps ‘values’, being already an array, does not need to be wrapped in another array. You can pass it directly as follows:

    let resp = await pool.query(query, values);

    Login or Signup to reply.
  2. 0

    The mysqljs/mysql module does not support bulk records via objects or arrays for the UPDATE method in the same way you are familiar with for the INSERT method. What you are imagining is not even a native feature in MySQL and at best can only be accomplished through CASE switching if you want to use the actual UPDATE method.

    That leaves you with 2 options:

    Option 1

    As long as your table and the data you provide for the update contain a unique key, you can use INSERT INTO table_name SET ? ON DUPLICATE KEY UPDATE …

    Option 2

    If you don’t have unique keyed fields and corresponding values in your query for a given table, you will have to build the query by iterating through your entries such that you end up with a string collection of however many UPDATE queries you will have – or – by building a query that leverages CASE conditions.

    You can see some examples here: How to create dynamic insert statement on the basis of POST body

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search