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]

[image] Kimo: WHERE [Name] = '"+Name+"'" that is not what I said to do... [image] 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;

This looks like a double posting - how is this different from your previous post? [image] Update SQL Table of multiple rows with different identifiers Share Your Projects 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…

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;

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. [image] Steve-Mcl: const whereClause = "(" + array.map(e => "'" + e + "'").join(",") + ");"

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};`

Node-RED

Node-RED Forum

How to update SQL query with an identifier has 2 strings

General

Steve-Mcl 19 August 2022 07:56 2

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

Topic		Replies	Views
Update SQL Table of multiple rows with different identifiers Share Your Projects	3	475	9 October 2022
mySQL, update 2 cells General	1	118	26 December 2023
SQLite update problem General	2	227	14 October 2022
Update More than one Value with SQLITE General	2	865	3 November 2018
Updating multiple SQL records at once General	5	482	6 December 2022

Copyright OpenJS Foundation and Node-RED contributors. All rights reserved. The OpenJS Foundation has registered trademarks and uses trademarks. For a list of trademarks of the OpenJS Foundation, please see our Trademark Policy and Trademark List. Trademarks and logos not indicated on the list of OpenJS Foundation trademarks are trademarks™ or registered® trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.

How to update SQL query with an identifier has 2 strings

Related topics