Can some help me with this sql statement which i want to insert into a function node, i would like also to use variables inplace 1 @ State and LRoom1 @ Device, but not yet. msg.topic="UPDATE MultiSenser SET State = 1 WHERE Device = LRoom1"; return msg; any help much appreciated

Welcome to the forum. A change node would also work: [Screenshot_2020-01-05_10-15-18]

You need single quotes round LRoom1 if that is a string rather than a column name.

If using a Change node then I don't think you should have the double quotes round the outside. The fact that the type is selected as a/z means that it knows it is a string. I don't think the mysql node needs the ; on the end of the statement, it will add that for you.

Thanks for reply, i would of never thought of using Change node, but the statement to insert is UPDATE MultiSenser SET State = 1 WHERE Device = "LRoom1" Thanks very much, but i cant use variables with a change node.

To insert variables in a string (if using a function node) you can use the new(ish) string template literals in javascript. https://appdividend.com/2019/01/23/javascript-template-literals-example-javascript-string-interpolation/ I think you could also do it in a Change node using JSONata but I will…

A tip I was given, which works really well for me, is to use the 'template' node (located in the function category) as it makes constructing the MySQL query fairly easy. Here's a very simple example. [ScreenShot063] [ScreenShot062]

If you need to run the same update statement lots of times with different variables, have a look at SQL prepared statements as they are a lot more efficient.

Node-RED

Node-RED Forum

MySQL statement help

General

Mark_Ellis 5 January 2020 10:44 6

Hi Colin Thanks for your reply, LRoom1 is row primary key for a row from Device column.

Topic		Replies	Views
Send data to MYSQL General	18	1221	16 May 2024
ISSUE with storing MQTT values to mySQL database : the values store as "undefined". Any guidence is much appreciated General database	10	827	13 May 2022
Insert data in sqlite without inject node General	3	666	26 January 2020
Use vars in Change Node General	12	765	28 August 2018
Building a dynamic msg.topic to create a mysql insert statement General	18	193	6 October 2025

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.

MySQL statement help

Related topics