# Sqlite UPDATE statement problem

**URL:** <https://discourse.nodered.org/t/sqlite-update-statement-problem/81850>\
**Category:** General\
**Created:** [7 October 2023 19:07 UTC](https://discourse.nodered.org/t/sqlite-update-statement-problem/81850 "2023-10-07T19:07:01Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Felix](https://avatars.discourse-cdn.com/v4/letter/f/aeb1de/32.png) [@Felix](https://discourse.nodered.org/u/Felix)\
**Post date:** [7 October 2023 19:07 UTC](https://discourse.nodered.org/t/sqlite-update-statement-problem/81850/1 "2023-10-07T19:07:01Z")

</div>

Hello all. I have problem with UPDATE statement in SQLITE node ...  
This script in function node follows SELECT :

```auto
var sel_song1 = flow.get("song_select1");// ID autoincrement value for identify
var sel_new1 = flow.get("song_new1");
var sel_num1 = flow.get("song_num1");
var sel_type1 = flow.get("song_type1");
var sel_title1 = flow.get("song_name1");
var sel_date1 = flow.get("song_date1");
var sel_text1 = flow.get("song_text1");
var sel_save1 = parseInt(flow.get("song_save1"));

if (sel_save1==1){// button SAVE
    if (sel_new1 == false) {// switch if you need new song, or UPDATE older

        msg.topic = 'UPDATE songs SET song_book_id = ' + sel_type1 + ', title = ' + sel_title1 + ', lyrics = ' + sel_text1 + ', song_number = ' + sel_num1 + ' , create_date = ' + sel_date1 + ' WHERE id = ' + sel_song1 + ';'
    }
    return msg;
}

```

at the msg output from function is SQLITE DB node with defined DB.  
I have googled some examples, but without running code. What I have defined wrong ??  
DB columns are defined correctly. If I SELECT from DB, this returns correct values from existing rows.  
Thanks for all reactions.

---

<div class="post-metadata">

**Author:** ![marcus-j-davies](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/marcus-j-davies/32/103435_2.png) [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)\
**Post date:** [7 October 2023 19:19 UTC](https://discourse.nodered.org/t/sqlite-update-statement-problem/81850/2 "2023-10-07T19:19:00Z")

</div>

Hi @Felix

You haven't actually stated what the problem is, 'I have problem with UPDATE' isn't much to go on.

Also, just a little feedback:  
You can make it a little easy to read, using literals : [Template literals (Template strings) - JavaScript | MDN](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals)

```auto
msg.topic = `UPDATE songs SET song_book_id = '${sel_type1}', title = '${sel_title2}', lyrics = '${sel_text1}', song_number = '${sel_num1}' , create_date = '{sel_date1}' WHERE id = '{sel_song1}';`

```

This potentually maybe the source of your problem - its just a guess 🤷‍♂️

You are using single quotes, to break between strings and variables - but shouldn't string values also use single quotes in SQLite (I have used them here) - string literals will solve that.

The single quotes are not used in the runtime evaluation (as it uses 'back ticks')  
Therefore SQLite will "see" the single quotes in the query

---

<div class="post-metadata">

**Author:** ![Felix](https://avatars.discourse-cdn.com/v4/letter/f/aeb1de/32.png) [@Felix](https://discourse.nodered.org/u/Felix)\
**Post date:** [7 October 2023 19:34 UTC](https://discourse.nodered.org/t/sqlite-update-statement-problem/81850/3 "2023-10-07T19:34:49Z")

</div>

I have tried it, but. SQLITE wrote mi back :

```auto
Error: SQLITE_ERROR: near "1.0": syntax error

```

So there is no way to success.

---

<div class="post-metadata">

**Author:** ![marcus-j-davies](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/marcus-j-davies/32/103435_2.png) [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)\
**Post date:** [7 October 2023 19:37 UTC](https://discourse.nodered.org/t/sqlite-update-statement-problem/81850/4 "2023-10-07T19:37:55Z")

</div>

I may have missed a few `$`

```auto
msg.topic = `UPDATE songs SET song_book_id = '${sel_type1}', title = '${sel_title2}', lyrics = '${sel_text1}', song_number = '${sel_num1}' , create_date = '${sel_date1}' WHERE id = '${sel_song1}'`

```

**EDIT**  
I am an MsSQL user, so may not be on the mark with the format of the query that is required, i.e does integers/decimals require being wrapped in quotes for example

```auto
msg.topic = `UPDATE songs SET song_book_id = '${sel_type1}', title = '${sel_title2}', lyrics = '${sel_text1}', song_number = ${sel_num1} , create_date = '${sel_date1}' WHERE id = ${sel_song1}`

```

---

<div class="post-metadata">

**Author:** ![Felix](https://avatars.discourse-cdn.com/v4/letter/f/aeb1de/32.png) [@Felix](https://discourse.nodered.org/u/Felix)\
**Post date:** [7 October 2023 19:57 UTC](https://discourse.nodered.org/t/sqlite-update-statement-problem/81850/5 "2023-10-07T19:57:20Z")

</div>

I have tried both, also I have checked all settings, but withou positive result.  
Here is a definition of table:  
id - INTEGER  
song\_book\_id - INTEGER  
title - TEXT  
lyrics - TEXT  
song\_number - TEXT  
create\_date - TEXT

Also I have updated it by this :

```auto
msg.topic = `UPDATE songs SET song_book_id = ${sel_type1}, title = '${sel_title1}', lyrics = '${sel_text1}', song_number = '${sel_num1}' , create_date = '${sel_date1}' WHERE id = ${sel_song1}`;

```

and update this :

```auto
var sel_song1 = parseInt(flow.get("song_select1"));// ID autoincrement value for identify
var sel_new1 = flow.get("song_new1");
var sel_num1 = flow.get("song_num1");
var sel_type1 = parseInt(flow.get("song_typ1"));
var sel_title1 = flow.get("song_name1");
var sel_date1 = flow.get("song_date1");
var sel_text1 = flow.get("song_text1");
var sel_save1 = parseInt(flow.get("song_save1"));

```

but without result.  
**Well** I am also PHP and MsSQL user and this makes me wrinkles ☹

---

<div class="post-metadata">

**Author:** ![marcus-j-davies](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/marcus-j-davies/32/103435_2.png) [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)\
**Post date:** [7 October 2023 20:00 UTC](https://discourse.nodered.org/t/sqlite-update-statement-problem/81850/6 "2023-10-07T20:00:09Z")

</div>

Does your values have single quotes?  
If it does - you need to use Parameters

Attach a `debug` node to the output of the `function` node

---

<div class="post-metadata">

**Author:** ![Felix](https://avatars.discourse-cdn.com/v4/letter/f/aeb1de/32.png) [@Felix](https://discourse.nodered.org/u/Felix)\
**Post date:** [7 October 2023 20:14 UTC](https://discourse.nodered.org/t/sqlite-update-statement-problem/81850/7 "2023-10-07T20:14:54Z")

</div>

So, here is screens :  
Debug nodes added few day back, but there is no messages on Debug node (all system messages and all check boxes of Debug node are checked). Only SQL error appears on click BUTTON SAVE as you read LOG1 in Debug25.

![screen1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/d/3d26d8f790bf81635740d8671d4747efa3ef6a37.jpeg)  
 ![screen2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/7/571dad7e5eb84e72902caa9dfb1ca3b8d2e4c48b.jpeg)

---

<div class="post-metadata">

**Author:** ![Felix](https://avatars.discourse-cdn.com/v4/letter/f/aeb1de/32.png) [@Felix](https://discourse.nodered.org/u/Felix)\
**Post date:** [7 October 2023 20:15 UTC](https://discourse.nodered.org/t/sqlite-update-statement-problem/81850/8 "2023-10-07T20:15:41Z")

</div>

Oh sorry, at the end of function node ..... I go for it ...

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [7 October 2023 20:16 UTC](https://discourse.nodered.org/t/sqlite-update-statement-problem/81850/9 "2023-10-07T20:16:30Z")

</div>

Set it to Output Complete Message.

---

<div class="post-metadata">

**Author:** ![Felix](https://avatars.discourse-cdn.com/v4/letter/f/aeb1de/32.png) [@Felix](https://discourse.nodered.org/u/Felix)\
**Post date:** [7 October 2023 20:26 UTC](https://discourse.nodered.org/t/sqlite-update-statement-problem/81850/10 "2023-10-07T20:26:06Z")

</div>

Here is :

```auto
7. 10. 2023 22:19:34 node: debug 17
UPDATE songs SET song_book_id = 13, title = 'Right here waiting', lyrics = '<?xml version='1.0' encoding='UTF-8'?><song version="1.0"><lyrics><verse label="1" type="v"><![CDATA[1.Oceans apart, day after day
And I slowly go insane
I hear your voice on the line
But it doesn't stop the pain
If I see you next to never
How can we say forever?
Wherever you go, whatever you do
I will be right here waiting for you
Whatever it takes or how my heart breaks
I will be right here waiting for you.
2.I took for granted all the times
That I thought would last somehow
I hear the laughter, I taste the tears
But I can't get near you now
Oh, can't you see it, baby?
You've got me goin' crazy
Wherever you go, whatever you do
I will be right here waiting for you
Whatever it takes or how my heart breaks
I will be right here waiting for you.
3.I wonder how we can survive
This romance
But in the end, if I'm with you
I'll take the chance
Oh, can't you see it, baby?
You've got me goin' crazy
Wherever you go, whatever you do
I will be right here waiting for you
Whatever it takes or how my heart breaks
I will be right here waiting for you.]]></verse></lyrics></song>', song_number = '4' , create_date = '2018-06-12 17:27:27' WHERE id = 12 : msg.payload : number
1

```

So debug node 17 is at the output of function node.

---

<div class="post-metadata">

**Author:** ![marcus-j-davies](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/marcus-j-davies/32/103435_2.png) [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)\
**Post date:** [7 October 2023 20:28 UTC](https://discourse.nodered.org/t/sqlite-update-statement-problem/81850/11 "2023-10-07T20:28:04Z")

</div>

> [@Felix](#):
>
> `lyrics`

Has a single quote - and is breaking the statement (I have not checked others)

```auto
<?xml version='1.0'

```

You need to use parameters

---

<div class="post-metadata">

**Author:** ![marcus-j-davies](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/marcus-j-davies/32/103435_2.png) [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)\
**Post date:** [7 October 2023 20:32 UTC](https://discourse.nodered.org/t/sqlite-update-statement-problem/81850/12 "2023-10-07T20:32:54Z")

</div>

```auto
msg.topic = 'UPDATE songs SET song_book_id = $sel_type1, title = $sel_title2, lyrics = $sel_text1, song_number = $sel_num1 , create_date = $sel_date1 WHERE id = $sel_song1'
msg.payload = [
  sel_type1,
  sel_title1,
  ... Others
]

```

---

<div class="post-metadata">

**Author:** ![Felix](https://avatars.discourse-cdn.com/v4/letter/f/aeb1de/32.png) [@Felix](https://discourse.nodered.org/u/Felix)\
**Post date:** [7 October 2023 20:33 UTC](https://discourse.nodered.org/t/sqlite-update-statement-problem/81850/13 "2023-10-07T20:33:16Z")

</div>

> [@Felix](#):
>
> `xml version='1.0' encoding='UTF-8'?><son`

Ohhh yes. Now understood the DB error :

```auto
"Error: SQLITE_ERROR: near "1.0": syntax error"

```

---

<div class="post-metadata">

**Author:** ![marcus-j-davies](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/marcus-j-davies/32/103435_2.png) [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)\
**Post date:** [7 October 2023 20:33 UTC](https://discourse.nodered.org/t/sqlite-update-statement-problem/81850/14 "2023-10-07T20:33:58Z")

</div>

See: [node-red-node-sqlite (node) - Node-RED](https://flows.nodered.org/node/node-red-node-sqlite)

 ![Screenshot 2023-10-07 at 21.33.50](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/4/84cc101525abd1a723e96fde5d011d884a5437f5.png)

---

<div class="post-metadata">

**Author:** ![Felix](https://avatars.discourse-cdn.com/v4/letter/f/aeb1de/32.png) [@Felix](https://discourse.nodered.org/u/Felix)\
**Post date:** [7 October 2023 20:45 UTC](https://discourse.nodered.org/t/sqlite-update-statement-problem/81850/15 "2023-10-07T20:45:24Z")

</div>

in UPDATE line without single quotes all **$sel\_**???

---

<div class="post-metadata">

**Author:** ![marcus-j-davies](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/marcus-j-davies/32/103435_2.png) [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)\
**Post date:** [7 October 2023 20:48 UTC](https://discourse.nodered.org/t/sqlite-update-statement-problem/81850/16 "2023-10-07T20:48:16Z")

</div>

The SQLlite node will replace any value with **$whatever** with the value that is in the payload array (and at the same index) - so it’s important to have them match the order of each other.

Topic = ‘Set name = $I\_like\_trains where id = $but\_dislike\_buses’

Payload = [‘marcus’, 100]

Will work for example - the order that $ appears will be the order of the element in the payload array

---

<div class="post-metadata">

**Author:** ![Felix](https://avatars.discourse-cdn.com/v4/letter/f/aeb1de/32.png) [@Felix](https://discourse.nodered.org/u/Felix)\
**Post date:** [7 October 2023 21:18 UTC](https://discourse.nodered.org/t/sqlite-update-statement-problem/81850/17 "2023-10-07T21:18:13Z")

</div>

> [@marcus-j-davies](#):
>
> The SQLlite node will replace any value with **$whatever** with the value that is in the payload array (and at the same index) - so it’s important to have them match the order of each other.

Added msg.payload :

```auto
 msg.topic = `UPDATE songs SET song_book_id = ${sel_type1}, title = ${sel_title1}, lyrics = ${sel_text1}, song_number = ${sel_num1} , create_date = ${sel_date1} WHERE id = ${sel_song1}`;
        msg.payload = [sel_type1, sel_title1, sel_text1, sel_num1, sel_date1];

```

But still not running. Maybe I am really tired. Here is late night hour.

---

<div class="post-metadata">

**Author:** ![marcus-j-davies](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/marcus-j-davies/32/103435_2.png) [@marcus-j-davies](https://discourse.nodered.org/u/marcus-j-davies)\
**Post date:** [7 October 2023 21:23 UTC](https://discourse.nodered.org/t/sqlite-update-statement-problem/81850/18 "2023-10-07T21:23:30Z")

</div>

You don’t need { } in this instance - as we are not using literals, just **$sel\_type1** etc

Example :  
song\_book\_id = $sel\_type1

---

<div class="post-metadata">

**Author:** ![Felix](https://avatars.discourse-cdn.com/v4/letter/f/aeb1de/32.png) [@Felix](https://discourse.nodered.org/u/Felix)\
**Post date:** [8 October 2023 14:13 UTC](https://discourse.nodered.org/t/sqlite-update-statement-problem/81850/19 "2023-10-08T14:13:38Z")

</div>

Marcus, thanks to show me part of way to get success. But nothing is done without hard work.  
This was the correct application of UPDATE statement :

```auto
msg.topic = `UPDATE songs SET song_book_id = ${sel_type1}, title = '${sel_title1}', lyrics = '${sel_text1}', song_number = '${sel_num1}' , create_date = '${sel_date1}' WHERE id = ${sel_song1}`;
msg.payload = [sel_type1, sel_title1, sel_text1, sel_num1, sel_date1];

```

to succesfull output, together with DB modification. It was necessary update all rows data under lyrics column to transfer text without XML tags and all is running now. This was performed by REPLACE command directly in SQLITE.  
Yeasterday late night for me was too tired to brainstorming. 🙂

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [22 October 2023 14:13 UTC](https://discourse.nodered.org/t/sqlite-update-statement-problem/81850/20 "2023-10-22T14:13:43Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.