Single MySQL query to modify multiple records using npm mysql package

Other Questions NPM
1

Hey folks! I’m struggling with a MySQL update. I want to change several records at once using one query. I’m using a Promise-based class for my database operations but it’s not working as expected.

Here’s what I’ve got:

// API endpoint
app.post('/confirm', (req, data) => {
  const db = new DBManager(dbConfig);
  const updateSql = 'UPDATE `unconfirmed_data` SET `source` = ? WHERE `item_id` = ?';
  const itemData = [req.body[0].source, req.body[0].item_id];

  db.startOperation([updateSql], itemData)
    .then(result => console.log(result))
    .catch(err => console.log(err));

  data.send('Changes applied');
});

// DBManager class
class DBManager {
  constructor(config) {
    this.conn = mysql.createConnection(config);
  }

  runQuery(sql, args) {
    return new Promise((resolve, reject) => {
      this.conn.query(sql, args ? [args] : null, (err, output) => {
        if (err) reject(err);
        else resolve(output);
      });
    });
  }

  startOperation(sqlList, args) {
    return new Promise((resolve, reject) => {
      this.conn.beginTransaction(err => {
        if (err) reject(err);
        // More code here...
      });
    });
  }
}

I’m getting a syntax error. Any ideas what I’m doing wrong? Thanks!

2

I see you’re having trouble updating multiple records with a single query. Here’s a solution that should work:

Modify your SQL query to use the IN clause:

UPDATE unconfirmed_data SET source = ? WHERE item_id IN (?)

Then, adjust your code like this:

const itemIds = req.body.map(item => item.item_id);
const source = req.body[0].source;
const itemData = [source, itemIds];

db.runQuery(updateSql, itemData)
.then(result => console.log(result))
.catch(err => console.log(err));

This approach will update all specified records in one go. Make sure to validate and sanitize your inputs to prevent SQL injection attacks. If you need to update with different sources for each item, you might want to use a transaction with multiple individual updates instead.

3

Hi Mia92, I’ve dealt with similar issues before. Your approach is close, but needs a few tweaks for updating multiple records.

First, modify your SQL query to use the IN clause:

UPDATE unconfirmed_data SET source = ? WHERE item_id IN (?)

Then, adjust your code to handle multiple items:

const itemIds = req.body.map(item => item.item_id);
const source = req.body[0].source;
const itemData = [source, itemIds];

db.runQuery(updateSql, itemData)
.then(result => console.log(result))
.catch(err => console.log(err));

This should update all specified records in one go. Remember to properly sanitize inputs to prevent SQL injection.

If you need different sources for each item, you might want to use a transaction with multiple individual updates instead. Let me know if you need help with that approach.

4

hey mia92, i think i see ur problem. ur trying to update multiple records but ur query is set up for just one. try using the IN operator in ur SQL like this:

UPDATE unconfirmed_data SET source = ? WHERE item_id IN (?)

then pass an array of item_ids as the second argument. that should do the trick!