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.
This looks like a double posting - how is this different from your previous post?
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
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)
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;
that is not what I said to do...
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;
Thank you so much, Steve. It solved my issue
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.
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.
Hi Colin. Nvermind, my mistake where I didn't see the semicolon after bracket which make it error. Thank you all.
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};`
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.
