# Function to create data and insert into column of existing sqlite DB

**URL:** <https://discourse.nodered.org/t/function-to-create-data-and-insert-into-column-of-existing-sqlite-db/73188>\
**Category:** General\
**Tags:** database\
**Created:** [4 January 2023 20:56 UTC](https://discourse.nodered.org/t/function-to-create-data-and-insert-into-column-of-existing-sqlite-db/73188 "2023-01-04T20:56:06Z")\
**Posts on this page:** 1\
**Showing post:** 5

<div class="post-metadata">

**Author:** ![Jacob](https://avatars.discourse-cdn.com/v4/letter/j/f1d935/32.png) [@Jacob](https://discourse.nodered.org/u/Jacob)\
**Post date:** [5 January 2023 17:42 UTC](https://discourse.nodered.org/t/function-to-create-data-and-insert-into-column-of-existing-sqlite-db/73188/5 "2023-01-05T17:42:14Z")

</div>

Thanks both, that were some really useful hints!!!!!  
I wasn't aware of the existence of "update" so using this now and its doing what I want, I checked for single values and that is working. I however remain with the problem, that I want to update the entire column of the table with values calculated in the function.

var max\_dist = 150; //meter  
var stat= 0;  
for (var i=0; i\<msg.length-4; i++) {  
var lat\_t= msg.payload[i].Latitude;  
var lat\_t\_1= msg.payload[i][i+1].Latitude;  
var dif\_lat= Math.abs(lat\_t)-Math.abs(lat\_t\_1)

```
var lon_t= msg.payload[i].Longitude;
var lon_t_1= msg.payload[i+1].Longitude;
var dif_lon= Math.abs(lon_t)-Math.abs(lon_t_1)

var dif_tot= Math.sqrt((Math.abs(dif_lat)-Math.abs(dif_lon))**2)*90000

if (dif_tot > max_dist) {stat=1;}
else if (((msg.payload[i+1].UNIX - msg.payload[i].UNIX) > 1000*60*10) ||((msg.payload[i+2].UNIX - msg.payload[i].UNIX) > 1000*60*10)||((msg.payload[i+3].UNIX - msg.payload[i][i].UNIX) > 1000*60*10)||((msg.payload[i+4].UNIX - msg.payload[i].UNIX) > 1000*60*10)){
    stat=0.5; }
else {stat=0;}

msg.payload[i].Status=stat;
id_set=msg.payload[i].id; 
} // here the loop ends and the calculated values are irrelevant since id_set and stat is given manually

```

id\_set=1211;  
stat=10;  
msg.topic="UPDATE gps SET Status= "+stat+" WHERE id = "+id\_set+";"  
return msg;

So I don't think its possible to loop over "msg.topic" and "return msg" to update all the entries ( msg.payload[i].Status or stat) at the position "id\_set"?  
This maybe refers to this post: [Update More than one Value with SQLITE - #3 by ilvesheimer](https://discourse.nodered.org/t/update-more-than-one-value-with-sqlite/4542/3)  
Anyone an idea how to solve this (I don't think I am able to go the batch way)? Originally with the code I wanted to create a string or array that holds all the values which than is of course as long as the table and than add/ update the entire column of the table with it.

---

_[View the full topic](https://discourse.nodered.org/t/function-to-create-data-and-insert-into-column-of-existing-sqlite-db/73188)._