Using variable in select query with Node and MySQL

Question

I need to import data from a table with mysql and node.

In a select statement I have to use a variable("var_destino" ) in the list of fields.

This variable come from req.body.

Is there a way to do it?

router.post("/importacao", async (req, res) => {
  const var_origem = req.body.origem;
  const var_destino = req.body.destino;
  let sql = `INSERT INTO cl_preco_material (
    id_mat,
    data_valor,
    valor,
    reducao,
    plano_id,
    tabela_mat,
    cod_mat)
    SELECT * FROM (
      SELECT id_mat,
      data_valor,
      valor,
      reducao,
      var_destino, //my problem is here
      tabela_mat,
      cod_mat from cl_preco_material B where B.plano_id=?) AS tmp
      WHERE NOT EXISTS (
         SELECT 1 FROM cl_preco_material K WHERE K.cod_mat = tmp.cod_mat and K.plano_id=?
      ) LIMIT 1`;
  try {
    await pool.query(sql, [var_origem, var_destino]);
    return res.send({ message: "OK" });
  } catch (e) {
    return res
      .status(400)
      .send("Error);
  }
});

slkorolev · Accepted Answer · 2020-06-30 20:11:45Z

1

I would try this:

await pool.query("SET @VAR_DESTINO = ?", [var_destino]);
await pool.query(sql, [var_origem]);

And mention @VAR_DESTINO in your bulk insert select clause

answered Jun 30, 2020 at 20:11

slkorolev

6,0111 gold badge32 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

George Guo Over a year ago

how to use "like" operator in sql in this case?

Collectives™ on Stack Overflow

Using variable in select query with Node and MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related