Update entry or create it to MariaDB (SQL Database)

General
1

Hey guys again,

my first issue was solved in an other thread here and to keep it a bit organized I just wanted to make a new thread on my following problem with my sql database. (old thread: Write variables to MariaDB (SQL Database) - General - Node-RED Forum (nodered.org))

According to this I was able to send a working topic/payload to the mysql database node to get an output without an error.

The problem I got now is that the entry for this specific device and room doesn't exist.
And I want to get something done like: If it exists, update it, if it doesn't create it.

This here is my working function node (for update) now:

const
    device = "deskLED",
    room = "office",
    value = 0;

msg.topic   = "UPDATE variables SET value = :value WHERE room = :room AND device = :device;"
msg.payload = {room, device, value}

return msg;

I played around with this syntax now and didn't get it to work:

msg.topic   = "INSERT INTO variables SET value = :value WHERE room = :room AND device = :device ON DUPLICATE KEY UPDATE value = : value;"
msg.payload = {room, device, value}

But yeah as I already told you, the syntax is wrong.

2

Does this work (assuming that room + device is a unique key)?
INSERT INTO variables SET room = :room, device = :device, value = :value ON DUPLICATE KEY UPDATE value = :value;"

I dislike the :something syntax, I build my sql commands like this:

var room = "bathroom";
var device = "shower";
var value = "wet";
msg.topic = "INSERT INTO 'variables' ('room', 'device', 'value') VALUES (room, device, value) ON DUPLICATE KEY UPDATE 'value' = value";

3

a device can be there mutliple times but only once per room. I guess the autoincrement value (id) is making this complicated here now right?

4

Yes. If room + device is unique then your table should be something like this with no "id"

create table test ( 
room varchar(10) not null, 
device varchar(10) not null, 
value varchar(10), 
primary key (room, device)
 )
1 Like
5

awesome, that made the trick!

6

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.