How to update SQL query with an identifier has 2 strings

[Kimo]

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.

[Steve-Mcl]

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

Update SQL Table of multiple rows with different identifiers

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? [image] Here attach a…

[Kimo]

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

[Steve-Mcl]

ok, in which case, you need to perform a specific SQL Query - information on how to is all over the web. e.g. here

In your case...

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)

[Kimo]

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?

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;

[Steve-Mcl]

@Kimo

WHERE [Name] = '"+Name+"'"

that is not what I said to do...

@Steve-Mcl

WHERE [name] IN ('A1','A2')

e.g..

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;

[Kimo]

Thank you so much, Steve. It solved my issue

[Kimo]

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

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.

[Colin]

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.

[Kimo]

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(",") + ");"

[Colin]

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)

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