1

I pass an SQL UPDATE statement a JSON object with less "columns" than the table. Why does the SQL statement update values of the "missing" columns as NULL?

I passing a JSON object to my object constructor which then sets the JSON key/values as its own.

If my JSON is missing a key-value pair (column), the constructor should not set its corresponding local variable. So why does the SQL UPDATE statement still update the missing "column" as NULL?

//---1st File---//
const Foo = require('.Foo');

let json = {
  oneKey: 'oneValue',
  twoKey: 'twoValue',
  // threeKey: 'threeValue' // <--- if this is commented out, ...
};

let foo = new Foo(json);
Foo.update(foo, callback);

function callback(err, data) {
  if (err) console.log(err);
  console.log(data);
}
//---End 1st File---//

//---2nd File---//
const pool = require('.sql');

let Foo = function(that) {
  this.oneKey = that.oneKey;
  this.twoKey = that.twoKey;
  this.threeKey = that.threeKey; // <--- then this local var should not be set in memory. Inspecting the object does not give threeKey with a empty value so I assume its not set in memory...
};

Foo.updateOne = function(foo, callback) {
  let sql = `UPDATE table_name SET ? WHERE oneKey = ?`;
  pool.query(sql, [foo, foo.oneKey], callback); // <--- but after query, column threeKey is updated as NULL. Why?
};

module.exports = Foo;
//---End 2nd File---//

//---This also does not work for 2nd File---//
const pool = require('.sql');

let Foo = function(that) {
  if (!that.oneKey) this.oneKey = that.oneKey; // <--- I tried doing something like this in case the above wasn't quite right...
  if (!that.twoKey) this.twoKey = that.twoKey;
  if (!that.threeKey) this.threeKey = that.threeKey;
};

Foo.updateOne = function(foo, callback) {
  let sql = `UPDATE table_name SET ? WHERE oneKey = ?`;
  pool.query(sql, [foo, foo.oneKey], callback); // <--- but after query, the column still updates as NULL.
};

module.exports = Foo;
//---End Example---//

What I expected to happen: SQL statement only updates column oneKey and twoKey, since threeKey is not defined.

What actually happens: SQL statement updates oneKey and twoKey (as it should), but also updates threeKey to NULL.

Deon Tan
  • 21
  • 2
  • Are these three (or two) properties the only properties on `this`? Of the properties that are defined, do you only want to assign a specific list of properties — e.g. `oneKey`, `twoKey` and `threeKey` — or _all_ properties? – Sebastian Simon Jul 03 '19 at 06:34
  • Hi @SebastianSimon I only want the SQL statement to update columns that the user defines in the JSON object. Assuming that the database only has these three columns, I excluded threeKey, but SQL statement still updates threeKey column as NULL, but I want that column to remain as whatever value it was before. – Deon Tan Jul 03 '19 at 06:49
  • What value are you expecting to see in the column? If no value exists in the json, why would the table row not have a null value? – Programnik Jul 03 '19 at 07:14
  • Check Bala Clark's answer for this [question](https://stackoverflow.com/questions/14992879/node-js-mysql-query-syntax-issues-update-where), it may help you – Anushanth.R Jul 03 '19 at 07:31
  • Probably `let Foo = function(that) { Obect.assign(this, that); };` will be sufficient. – Sebastian Simon Jul 03 '19 at 07:37
  • @SebastianSimon I posted my answer, I will try out your method and see if it works too. Edit: SQL statement still updates fields as NULL. – Deon Tan Jul 03 '19 at 08:23

1 Answers1

0

This is the workaround I made. Basically query the database first, then replace all undefined values with values from the database.

const pool = require('./sql');
const _ = require('lodash');

Foo.readOne = function (foo, callback) {
    pool.query("SELECT * FROM table_name WHERE oneKey = ?", foo.oneKey, callback);
};

Foo.updateOne = function (foo, callback) {
    Foo.readOne(foo, function (err, data) {
        if (err) return callback(err);
        if (_.isEmpty(data)) return callback(new Error(`oneKey ${foo.oneKey} does not exist in the database!`))
        _.forEach(Object.keys(foo), key => {
            if (typeof foo[key] === 'undefined') foo[key] = data[0][key];
        })
    });
    pool.query("UPDATE table_name SET ? WHERE oneKey = ?", [foo, foo.oneKey], callback);
}
Deon Tan
  • 21
  • 2