CODEWITHSUNDEEP
mysqlnode.jsinsert-updateupsert
Kirindage Gayashan
Kirindage Gayashan

Reputation: 11

How to insert a record if not existing and update if existing (upsert) using node js mysql?

This is my insert query and now I want it to modify it using UPSERT method to check whether the reocord is existing then update if not existing insert.Here is my insert part.

jsonObj = JSON.parse(jsonData);

if (jsonObj.hasOwnProperty("PE")) {
    msgId = parseInt(jsonObj["PE"]);

    if (msgId == XEPT_EVENT_ID) {
        var pe = jsonObj["PE"];
        var uid = jsonObj["UID"];
        var mac = jsonObj["MAC"];
        var time = jsonObj["TIM"];

        var records = jsonObj["Record"];
        var digital_input = [];
        records.forEach(rec => {
            digital_input.push(rec[3]);
        });

        if (typeof con !== 'undefined' && con) {
            con.connect();
            var sql = "INSERT INTO  sierra_ips.iot_output_log (device_code,  device_id, last_updated_time, digital_input_0, digital_input_1, digital_input_2, digital_input_3, digital_input_4, digital_input_5, digital_input_6, digital_input_7)VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            con.query(sql, [uid, mac, time, digital_input[0], digital_input[1], digital_input[2], digital_input[3], digital_input[4], digital_input[5], digital_input[6], digital_input[7]], 
                function (err, result) {
                    if (err) throw err;
                    console.log("Record inserted");
                }
            );
        }
    }
}

Upvotes: 0

Views: 700

Answers (1)

Kirindage Gayashan
Kirindage Gayashan

Reputation: 11

I got answer using this way,

var mysql = require('mysql');
const upsert = require('mysql-upsert');

 const table = 'table name'
 const data = [
   { col_name: 'value' }]#your data with columns

var con = mysql.createConnection({
 host: "",
  user: "",
  password: "b",
  database: ""#config db connection
});

const { affectedRows } = upsert(con)(table, data)
con.end()

Here is reference link = [https://www.npmjs.com/package/mysql-upsert][1]

Upvotes: 1

Related Questions