# Update query in sqlite3 with fob id coming from gateway code

**URL:** <https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292>\
**Category:** General\
**Created:** [1 September 2020 10:08 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292 "2020-09-01T10:08:49Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [1 September 2020 10:08 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/1 "2020-09-01T10:08:49Z")

</div>

I have to update sqlite3 query with Door status and only on that column which is coming from successfully database entry

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [1 September 2020 10:11 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/2 "2020-09-01T10:11:44Z")

</div>

what is your question exactly and please provide some information that people can work with.

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [1 September 2020 10:14 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/3 "2020-09-01T10:14:00Z")

</div>

hello Sir,  
I am taking fob id from gateway side and checking that with my sqlite3 database ,it fob id is present CR\_SUCCESS is showing and if not CR\_FAIL is showing.

Now my query is I want to change the door status like on/off and whatever the status is it should be updated in my sqlite3 back with the successful FOB id.

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [1 September 2020 10:25 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/4 "2020-09-01T10:25:45Z")

</div>

please sir help in this.

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [1 September 2020 10:46 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/5 "2020-09-01T10:46:43Z")

</div>

UPDATE NCS\_DATABASE SET Status='DR1\_OFF' WHERE Person\_ID='undefined' : msg.payload : undefined

undefined

this undefined i am getting???

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [1 September 2020 11:13 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/6 "2020-09-01T11:13:12Z")

</div>

UPDATE NCS\_DATABASE SET Status='DR1\_OFF' WHERE Person\_ID=' 0 4 2 2 2 4 3 6 ' : msg.payload : array[0]

[empty]

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [1 September 2020 11:13 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/7 "2020-09-01T11:13:39Z")

</div>

now this much i reached but empty it is showing why??

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [1 September 2020 11:38 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/8 "2020-09-01T11:38:49Z")

</div>

> [@shipra](#):
>
> UPDATE NCS\_DATABASE SET Status='DR1\_OFF' WHERE Person\_ID=' 0 4 2 2 2 4 3 6 '

Shipra .. because i happen to know from your previous topic regarding the sqlite error. array[0]  
This happens, and you don't get an **update in sqlite** because your db has the **Person\_ID without spaces.** you are trying to update with spaces in id **0 4 2 2 2 4 3 6**

Did you read the function we used for the **SELECT** queries ? Its named "id no space"  
The same applies for **UPDATE** queries which i guess you are trying to do now.

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [1 September 2020 11:43 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/9 "2020-09-01T11:43:08Z")

</div>

ok sir i will do this and update you.Thank you

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [1 September 2020 11:57 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/10 "2020-09-01T11:57:01Z")

</div>

Sir check my flow.and suggest where i am wrong

 ![2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/1/f1072ea66d44eb04cc0b187f0cc1af28308f21ca.png)

now space is not coming ,but still empty is showing

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [1 September 2020 12:16 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/11 "2020-09-01T12:16:52Z")

</div>

The Update query looks ok.  
Are you sure the record wasnt updated ?  
does Person\_ID='04222436' actually exist so it can be updated ? 😉

what you have in function before the sqlite3 node ?  
use ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/d/d56639561f3c9fa2f21f979311d7d4edcc5a9ef1.png)

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [1 September 2020 12:29 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/12 "2020-09-01T12:29:47Z")

</div>

```auto

let newMsg = {};
newMsg.topic = `UPDATE NCS_DATABASE SET Status='${msg.payload}' WHERE Person_ID='${msg.id}'`;
return newMsg;

```

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [1 September 2020 12:30 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/13 "2020-09-01T12:30:05Z")

</div>

If it helps you can install a utility called **[DB Browser for SQLITE](https://sqlitebrowser.org/)** so you can visually check your database.

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [1 September 2020 12:33 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/14 "2020-09-01T12:33:37Z")

</div>

```auto
shipra@shipra-HP-15-Notebook-PC:~/Documents/NCS_Projects/IOT/NCS_API$ sqlite3 NCS.db 
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> .tables
NCS_DATABASE
sqlite> SELECT * FROM NCS_DATABASE;
1| | | |04222436| |2813| | | 
2| | | |04222437| |2814| | | 
sqlite> SELECT * FROM NCS_DATABASE;
1| | | |04222436| |2813| | | 
2| | | |04222437| |2814| | | 
sqlite> 

```

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [1 September 2020 12:35 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/15 "2020-09-01T12:35:48Z")

</div>

Sir 04222436 exist in DB thats y CR\_SUCCESS was coming then only i have added further flow.

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [1 September 2020 12:41 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/16 "2020-09-01T12:41:43Z")

</div>

Is there a command to show the **Columns** of the NCS\_DATABASE table ?  
is **Status** a column in that table?

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [1 September 2020 12:43 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/17 "2020-09-01T12:43:12Z")

</div>

```auto
sqlite> .schema NCS_DATABASE
CREATE TABLE NCS_DATABASE(SrNo INT NOT NULL,Date_and_Time TEXT NOT NULL,Event_Type TEXT NOT NULL,Event_Description TEXT NOT NULL,Person_ID TEXT NOT NULL,Person_Name_Description TEXT NOT NULL,Fob_Tag_ID TEXT NOT NULL,Door_ID TEXT NOT NULL,Door_Description TEXT NOT NULL,Status TEXT NOT NULL);

```

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [1 September 2020 12:45 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/18 "2020-09-01T12:45:33Z")

</div>

After logging into **sql** prompt mode, execute the following commands...

```auto
.header on
.mode column

```

...then queries should be returned with headers.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [1 September 2020 12:49 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/19 "2020-09-01T12:49:21Z")

</div>

@shipra Take a look at what your query is, then take a look at the data you dumped from the database is and tell us what is different.

---

<div class="post-metadata">

**Author:** ![shipra](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@shipra](https://discourse.nodered.org/u/shipra)\
**Post date:** [1 September 2020 12:52 UTC](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292/20 "2020-09-01T12:52:16Z")

</div>

```auto
1| | | |04222436| |2813| | |"DR1_ON"
2| | | |04222437| |2814| | | 

```

Sir its coming Thank you litlle bit " " this quotes was missing

[Next page](https://discourse.nodered.org/t/update-query-in-sqlite3-with-fob-id-coming-from-gateway-code/32292.md?page=2)