I am not that much into sql and was working on my first project

I read about updating queries from tutorial points

To start with I created a helper function which looks like this

const updateFieldInTable = (tableName, conditionParameter, updatedCondition, locationReference, locationReferenceValue) => {
  return new Promise((resolve, reject) => {
    pool.getConnection((error, connection) => {
      if (error) return reject(error)
      const query = `UPDATE ${tableName} SET ${conditionParameter} = ${updatedCondition} WHERE ${locationReference} = ${locationReferenceValue}`
      connection.query(query, (error, response) => {
        connection.destroy()
        if (error) return reject(error)
        return resolve(response)
      })
    })
  })
}

I am using this to update field in my table, So I create a dummy route to perform this task for me and to see if this works or not

app.get('/test', async (req, res) => {
  const resultFromQuery = await updateFieldInTable('personal', 'gradYear', 2017, 'userId', 1234)
  console.log(`Result from Query:`,  resultFromQuery)
  res.status(200).json(resultFromQuery[0])
});

The above query works perfectly fine, but If i change it to a varchar, I am getting the following error

Error: ER_BAD_FIELD_ERROR: Unknown column 'ryan' in 'field list'

Here is what is giving me the error

app.get('/test', async (req, res) => {
  const resultFromQuery = await updateFieldInTable('personal', 'school', 'ryan', 'userId', 1234)
  console.log(`Result from Query:`,  resultFromQuery)
  res.status(200).json(resultFromQuery[0])
});

This is how my sql dumb for the same would look like

 `gradYear` int(4) DEFAULT NULL,
  `gradMonth` enum('january','february','march','april','may','june','july','august','september','october','november','december') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `school` varchar(255) DEFAULT NULL,
  `degree` varchar(255) DEFAULT NULL,

I am not that much into sql and was working on my first project

I read about updating queries from tutorial points

To start with I created a helper function which looks like this

const updateFieldInTable = (tableName, conditionParameter, updatedCondition, locationReference, locationReferenceValue) => {
  return new Promise((resolve, reject) => {
    pool.getConnection((error, connection) => {
      if (error) return reject(error)
      const query = `UPDATE ${tableName} SET ${conditionParameter} = ${updatedCondition} WHERE ${locationReference} = ${locationReferenceValue}`
      connection.query(query, (error, response) => {
        connection.destroy()
        if (error) return reject(error)
        return resolve(response)
      })
    })
  })
}

I am using this to update field in my table, So I create a dummy route to perform this task for me and to see if this works or not

app.get('/test', async (req, res) => {
  const resultFromQuery = await updateFieldInTable('personal', 'gradYear', 2017, 'userId', 1234)
  console.log(`Result from Query:`,  resultFromQuery)
  res.status(200).json(resultFromQuery[0])
});

The above query works perfectly fine, but If i change it to a varchar, I am getting the following error

Error: ER_BAD_FIELD_ERROR: Unknown column 'ryan' in 'field list'

Here is what is giving me the error

app.get('/test', async (req, res) => {
  const resultFromQuery = await updateFieldInTable('personal', 'school', 'ryan', 'userId', 1234)
  console.log(`Result from Query:`,  resultFromQuery)
  res.status(200).json(resultFromQuery[0])
});

This is how my sql dumb for the same would look like

 `gradYear` int(4) DEFAULT NULL,
  `gradMonth` enum('january','february','march','april','may','june','july','august','september','october','november','december') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `school` varchar(255) DEFAULT NULL,
  `degree` varchar(255) DEFAULT NULL,

• javascript
• mysql
• node.js

1 Answer 1

When you want to insert/update string value on db it should be between single quotes. With your parameters sql seems like SET school = ryan but it should be SET school = 'ryan'. So send ryan value to your function like '\'ryan\'' or "'ryan'"