[SOLVED] Can't do bulk update using MariaDB, pool.query and Express

Can't do bulk update using MariaDB, pool.query and Express

I have an API written in Express and MariaDB as DBMS, I'm trying to update multiple records at the same time using an array but it doesn't work. Example:

const data = [ [ '2', '130' ], [ '4', '10' ] ]
try {
const updatedInventory = await pool.query('UPDATE inventory SET qty_shipped = qty_shipped + VALUES ? WHERE sku_id = VALUES ?', [data])
res.status(201).json(updatedInventory)
} catch (error) {
    res.status(500).json({ message: error.message })
    console.error(error.message)
}

However I get this error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'VALUES ('2', '130'), ('4', '10') WHERE sku_id = VALUES ?' at line 1

I did something similar with the Insert endpoint where I'm doing bulk inserts also using an array and that works just fine:

const newInventory = await pool.query('INSERT INTO inventory (sku_id, qty_received, expiration_date) VALUES ? ON DUPLICATE KEY UPDATE qty_received = qty_received + VALUES(qty_received) ', [data])

Any ideas on how to get the bulk update part working?

Solution

According to the error you put in the question, the second "?" isn't grounded. If you look at the error, you see that the first value set is grounded to the data variable, but the second one isn't.

If you expect to ground the data to both "?" in your statement, then you might need to add the data variable to your statement twice.

Also, it might be that you can't ground the data variable twice. I wouldn't expect that; you should be able to use the data variable twice in our statement. If you need to different variables, then create a second variable, copy data into the second variable and then ground both both variables in your statement.