# How to update SQL query with an identifier has 2 strings

**URL:** <https://discourse.nodered.org/t/how-to-update-sql-query-with-an-identifier-has-2-strings/66647>\
**Category:** General\
**Created:** [19 August 2022 07:47 UTC](https://discourse.nodered.org/t/how-to-update-sql-query-with-an-identifier-has-2-strings/66647 "2022-08-19T07:47:57Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![Kimo](https://avatars.discourse-cdn.com/v4/letter/k/848f3c/32.png) [@Kimo](https://discourse.nodered.org/u/Kimo)\
**Post date:** [19 August 2022 07:47 UTC](https://discourse.nodered.org/t/how-to-update-sql-query-with-an-identifier-has-2-strings/66647/1 "2022-08-19T07:47:57Z")

</div>

Hi.  
Anyone knows here is it possible if we can update sql table where the query with an id has 2 strings. The result I got is only A2 string is taken as identifier thus row contains id A1 is not updated.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/5/75ee04f0213dde8c1c6fa1341af60803d7bb3f0e.png)

---

<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:** [19 August 2022 07:56 UTC](https://discourse.nodered.org/t/how-to-update-sql-query-with-an-identifier-has-2-strings/66647/2 "2022-08-19T07:56:35Z")

</div>

This looks like a double posting - how is this different from your previous post?

> [@Update SQL Table of multiple rows with different identifiers](https://discourse.nodered.org/t/update-sql-table-of-multiple-rows-with-different-identifiers/66632):
>
> Hi. I been stuck for some days figuring out how I can update multiple rows of a sql table which have different identifier. Inside my flow, when user key in an input, the data will send to sql table also to a ui table the same time as shown below with red area. Then, user will press start to log the start timestamp and update the timestamp into sql table. However, if user can submit data multiple times bfore pressing start, how I can update these data with same timestamp? Here attach a…

---

<div class="post-metadata">

**Author:** ![Kimo](https://avatars.discourse-cdn.com/v4/letter/k/848f3c/32.png) [@Kimo](https://discourse.nodered.org/u/Kimo)\
**Post date:** [19 August 2022 08:15 UTC](https://discourse.nodered.org/t/how-to-update-sql-query-with-an-identifier-has-2-strings/66647/3 "2022-08-19T08:15:29Z")

</div>

Previous one I post before I found a way to map a property from 2 objects in array and convert it into string. So I thought it might be different topic to discuss thus I post here

---

<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:** [19 August 2022 08:28 UTC](https://discourse.nodered.org/t/how-to-update-sql-query-with-an-identifier-has-2-strings/66647/4 "2022-08-19T08:28:57Z")

</div>

ok, in which case, you need to perform a specific SQL Query - information on how to is all over the web. e.g. [here](https://stackoverflow.com/questions/62934688/how-to-update-all-rows-where-id-is-in-array)

In your case...

> ```auto
> WHERE [name] IN ('A1','A2')
> 
> ```

NOTE: this is better done with parameters in the mssql-plus node.

PS - in future, please post CODE as TEXT **not** as a SCREENSHOT (saves anyone helping you re-typing what is in your screenshot)

---

<div class="post-metadata">

**Author:** ![Kimo](https://avatars.discourse-cdn.com/v4/letter/k/848f3c/32.png) [@Kimo](https://discourse.nodered.org/u/Kimo)\
**Post date:** [19 August 2022 08:52 UTC](https://discourse.nodered.org/t/how-to-update-sql-query-with-an-identifier-has-2-strings/66647/5 "2022-08-19T08:52:32Z")

</div>

Thank you Steve for the answer. I already read that information, but thank you for sharing it here.  
My case now the identifier is coming from an array which generated by user multiple times.  
If I do like my code below, only A2 or the latest data gets updated in sql. Any idea on this?

```auto
var array = flow.get ('array');
const output = array.map(({ Name }) => Name); // Take 'Name' property from array objects and create array
var Name = output.toString(); //Convert array into string -- Result: "A1,A2, ..."

var now = new Date().toLocaleString("en-GB");
var Start_Timestamp = now;

msg.payload = "UPDATE [dbo].[Background] SET [Start_Time] = '" + Start_Timestamp + "' WHERE [Name] = '"+Name+"'" ;
return msg;

```

---

<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:** [19 August 2022 09:04 UTC](https://discourse.nodered.org/t/how-to-update-sql-query-with-an-identifier-has-2-strings/66647/6 "2022-08-19T09:04:15Z")

</div>

> [@Kimo](#):
>
> `WHERE [Name] = '"+Name+"'" `

that is not what I said to do...

> [@Steve-Mcl](#):
>
> `WHERE [name] IN ('A1','A2')`

e.g..

```auto
const array = flow.get('array'); //example ["A1", "A2"];
if(!array || !array.length) {
    node.warn("array is empty");
    return null // halt flow
}
const whereClause = "(" + array.map(e => "'" + e + "'").join(",") + ");"
const now = new Date().toLocaleString("en-GB");
msg.payload = "UPDATE [dbo].[Background] SET [Start_Time] = '" + now + "' WHERE [Name] in " + whereClause;
return msg;

```

---

<div class="post-metadata">

**Author:** ![Kimo](https://avatars.discourse-cdn.com/v4/letter/k/848f3c/32.png) [@Kimo](https://discourse.nodered.org/u/Kimo)\
**Post date:** [21 August 2022 09:11 UTC](https://discourse.nodered.org/t/how-to-update-sql-query-with-an-identifier-has-2-strings/66647/7 "2022-08-21T09:11:31Z")

</div>

Thank you so much, Steve. It solved my issue

---

<div class="post-metadata">

**Author:** ![Kimo](https://avatars.discourse-cdn.com/v4/letter/k/848f3c/32.png) [@Kimo](https://discourse.nodered.org/u/Kimo)\
**Post date:** [21 August 2022 17:01 UTC](https://discourse.nodered.org/t/how-to-update-sql-query-with-an-identifier-has-2-strings/66647/8 "2022-08-21T17:01:52Z")

</div>

Sorry, one more Steve, after succeed with previous one, then I think if I have to use AND operator after the IN condition bcs I need to use more than one identifiers, and I got error shown

```auto
msg.payload = "UPDATE [dbo].[Background] SET [Start_Time] = '" + Start_Timestamp + "' WHERE [Name] IN "+Name+" AND [ID_Number] IN "+ID+" " ;

```

> {"class":15,"code":"EREQUEST","lineNumber":1,"message":"Incorrect syntax near the keyword 'and'.","details":"Incorrect syntax near the keyword 'and'.","name":"RequestError","number":156,"procName":"","serverName":"-------","state":1}

So just to clarify, is it not allowed in sql query to combine AND operator and the IN condition at the same time? Bcs I've been trying to modify the query but also seems not working so far.

---

<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:** [21 August 2022 20:36 UTC](https://discourse.nodered.org/t/how-to-update-sql-query-with-an-identifier-has-2-strings/66647/9 "2022-08-21T20:36:20Z")

</div>

Once again, please feed the function output into a debug node so we can see exactly what topic you are passing to the sql node.

---

<div class="post-metadata">

**Author:** ![Kimo](https://avatars.discourse-cdn.com/v4/letter/k/848f3c/32.png) [@Kimo](https://discourse.nodered.org/u/Kimo)\
**Post date:** [22 August 2022 02:52 UTC](https://discourse.nodered.org/t/how-to-update-sql-query-with-an-identifier-has-2-strings/66647/10 "2022-08-22T02:52:23Z")

</div>

Hi Colin. Nvermind, my mistake where I didn't see the semicolon after bracket which make it error. Thank you all.

> [@Steve-Mcl](#):
>
> `const whereClause = "(" + array.map(e => "'" + e + "'").join(",") + ");"`

---

<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:** [22 August 2022 08:14 UTC](https://discourse.nodered.org/t/how-to-update-sql-query-with-an-identifier-has-2-strings/66647/11 "2022-08-22T08:14:39Z")

</div>

Often when coding assignments like this is it easier to use the template literal syntax. It is much easier to see how the quotes match up. So something like (untested)

```auto
msg.payload = `UPDATE [dbo].[Background] SET [Start_Time] = '${Start_Timestamp}' WHERE [Name] IN ${Name} AND [ID_Number] IN ${ID};`

```

---

<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:** [5 September 2022 08:14 UTC](https://discourse.nodered.org/t/how-to-update-sql-query-with-an-identifier-has-2-strings/66647/12 "2022-09-05T08:14:56Z")

</div>

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