Node.js / SQL: Insert only new values

Question

I have a small DB where i insert new data to each column at a different time. Because I'm only inserting one new value, the values for other columns will become NULL. But if thats the case, i want to keep the old value.

My DB looks like this:

One solution would be using coalesce() i guess, but i'm updating each column dynamically, and so the other column names are unknown.

function database_call(request) {
  database.query(request, function (err, result) {
    if (err) {
      console.log(err);
    }
  });
}

subscribedItem.on("changed", function (dataValue) {
  let databaseAttribute = subscribedItem.itemToMonitor.nodeId.value;
  let databaseValue = dataValue.value.value;
  databaseAttribute = databaseAttribute.substring(databaseAttribute.indexOf('.')+1)
  databaseAttribute = databaseAttribute.replace(".", '');
  databaseAttribute = databaseAttribute.replace(/"/g, '');
  database_call("INSERT INTO Prozessdaten ("+databaseAttribute+") VALUES ("+databaseValue+")");
});

Could you add some information? Like an example of what happens right now e what would you obtain? — oniramarf
– oniramarf, Commented Jan 17, 2019 at 10:58
this code is inserting values like its shown in the database above. i don't know how to implement the coalesce() method dynamically because you need to know each others row attribute name. — David
– David, Commented Jan 17, 2019 at 11:00
What you're saying is that you don't know all the columns available in the table when doing your code? Because that's not a case I'd expect in a database that uses SQL. — oniramarf
– oniramarf, Commented Jan 18, 2019 at 10:40
i know all columns available but I'm only updating one column value at a time (as you see in the picture)... I'm not receving any new values for other columns. I need to keep the previous value for columns which I'm not updating. I could solve this by querying the previous database record. but thats inefficient. — David
– David, Commented Jan 18, 2019 at 11:21

oniramarf · Accepted Answer · 2019-01-18 11:34:10Z

1

I've found this that implements a 'vertical' coalesce.

You should first do a query like this, using SUBSTRING_INDEX and GROUP_CONCAT to obtain the latest not-null value available in the database for each column.

SELECT 
    SUBSTRING_INDEX(GROUP_CONCAT(05Hz ORDER BY ID DESC SEPARATOR '@@INDEX@@'), '@@INDEX@@', 1) AS 05Hz,
    SUBSTRING_INDEX(GROUP_CONCAT(5Hz ORDER BY updated_at DESC SEPARATOR '@@INDEX@@'), '@@INDEX@@', 1) AS 5Hz
FROM
    table
LIMIT 1

After that, update the single value you really need to update and perform an insert specifying all the values for every column.

answered Jan 18, 2019 at 11:34

oniramarf

9141 gold badge12 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

Node.js / SQL: Insert only new values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related