MySQL statement help

[Mark_Ellis]

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

[ghayne]

Welcome to the forum. A change node would also work:

[Colin]

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

[Colin]

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.

[Mark_Ellis]

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.

[Mark_Ellis]

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

[Colin]

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 leave it for someone else to tell you how to do that.

[dynamicdave]

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.

[TotallyInformation]

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.