# Update entry or create it to MariaDB (SQL Database)

**URL:** <https://discourse.nodered.org/t/update-entry-or-create-it-to-mariadb-sql-database/49456>\
**Category:** General\
**Created:** [7 August 2021 17:23 UTC](https://discourse.nodered.org/t/update-entry-or-create-it-to-mariadb-sql-database/49456 "2021-08-07T17:23:11Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![nicedevil](https://avatars.discourse-cdn.com/v4/letter/n/858c86/32.png) [@nicedevil](https://discourse.nodered.org/u/nicedevil)\
**Post date:** [7 August 2021 17:23 UTC](https://discourse.nodered.org/t/update-entry-or-create-it-to-mariadb-sql-database/49456/1 "2021-08-07T17:23:11Z")

</div>

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)](https://discourse.nodered.org/t/write-variables-to-mariadb-sql-database/49452/11))

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:

```auto
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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [7 August 2021 17:55 UTC](https://discourse.nodered.org/t/update-entry-or-create-it-to-mariadb-sql-database/49456/2 "2021-08-07T17:55:44Z")

</div>

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";

---

<div class="post-metadata">

**Author:** ![nicedevil](https://avatars.discourse-cdn.com/v4/letter/n/858c86/32.png) [@nicedevil](https://discourse.nodered.org/u/nicedevil)\
**Post date:** [7 August 2021 17:57 UTC](https://discourse.nodered.org/t/update-entry-or-create-it-to-mariadb-sql-database/49456/3 "2021-08-07T17:57:42Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [7 August 2021 18:06 UTC](https://discourse.nodered.org/t/update-entry-or-create-it-to-mariadb-sql-database/49456/4 "2021-08-07T18:06:51Z")

</div>

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

```auto
create table test ( 
room varchar(10) not null, 
device varchar(10) not null, 
value varchar(10), 
primary key (room, device)
 )

```

---

<div class="post-metadata">

**Author:** ![nicedevil](https://avatars.discourse-cdn.com/v4/letter/n/858c86/32.png) [@nicedevil](https://discourse.nodered.org/u/nicedevil)\
**Post date:** [7 August 2021 18:11 UTC](https://discourse.nodered.org/t/update-entry-or-create-it-to-mariadb-sql-database/49456/5 "2021-08-07T18:11:08Z")

</div>

awesome, that made the trick!

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [21 August 2021 18:11 UTC](https://discourse.nodered.org/t/update-entry-or-create-it-to-mariadb-sql-database/49456/6 "2021-08-21T18:11:28Z")

</div>

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