# UPDATE record from flow to MariaDB database

**URL:** <https://discourse.nodered.org/t/update-record-from-flow-to-mariadb-database/20556>\
**Category:** General\
**Created:** [19 January 2020 02:03 UTC](https://discourse.nodered.org/t/update-record-from-flow-to-mariadb-database/20556 "2020-01-19T02:03:25Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![baqwas](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/baqwas/32/106_2.png) [@baqwas](https://discourse.nodered.org/u/baqwas)\
**Post date:** [19 January 2020 02:03 UTC](https://discourse.nodered.org/t/update-record-from-flow-to-mariadb-database/20556/1 "2020-01-19T02:03:25Z")

</div>

Hello,

I've been using the **mysql** node for _INSERT_ and _CALL_ operations successfully but have reached a roadblock in understanding how to use it for the equivalent of the SQL _UPDATE_ statement. The _INSERT_ code snippet has been working for several years now. A sample flow is captured below:

```auto
[{"id":"574ac50e.0e43fc","type":"rpi-sensehat in","z":"202ea5e3.ae1b3a","name":"PiSH-11-1","motion":false,"env":true,"stick":false,"x":60,"y":260,"wires":[["fff6066a.aa9708"]]},{"id":"fff6066a.aa9708","type":"delay","z":"202ea5e3.ae1b3a","name":"HoldEm","pauseType":"rate","timeout":"1","timeoutUnits":"minutes","rate":"1","nbRateUnits":"1","rateUnits":"minute","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":true,"x":200,"y":260,"wires":[["7d44886b.f584b8"]]},{"id":"7d44886b.f584b8","type":"function","z":"202ea5e3.ae1b3a","name":"PackEm","func":"// an experiment in using basic SQL statements rather an \"efficient\" stored procedure\n// appropriate \"stored\" procedure exists, used in other flows\nnode.status({fill:\"yellow\", shape:\"ring\", text:\"processing\"});\n// special case for current PiSH-11-1; TBD: stored procedure for general case\nvar currentStatus = 0;\nvar deviceIdT = 1453;\nvar deviceIdH = 1454;\nvar deviceIdP = 1455;\nvar dtStamp = new Date();\nvar ISOstrTZ = dtStamp.toISOString();\n// Datetime formats:\n// - MariaDB, MySQL: \"2019-07-04 03:27:22.54\";\n// - Javascript ISO: \"2019-07-04T03:27:22.54Z\";\n// very embarassing code below; shows how little I know about Javascript\nvar ISOstrZ = ISOstrTZ.replace(\"T\", \" \");\nvar waqt = ISOstrZ.replace(\"Z\", \"\");\n\n// INSERT Telemetry\nvar msgTelemetry = {};\nmsgTelemetry.topic = \"INSERT INTO Telemetry \" +\n \"(Waqt, DeviceId, Status, Reading, Dimension, UOM) \" +\n \"VALUES ?\";\nvar newTelemetry = [\n [waqt, deviceIdT, currentStatus, msg.payload.temperature, \"Temperature\", \"\\xB0C\"],\n [waqt, deviceIdH, currentStatus, msg.payload.humidity, \"RHumidity\", \"%rH\"],\n [waqt, deviceIdP, currentStatus, msg.payload.pressure, \"Pressure\", \"mb\"]\n ];\nmsgTelemetry.payload = [newTelemetry];\n\nvar txTime = dtStamp.toLocaleTimeString('en-US');\nnode.status({fill:\"green\", shape:\"ring\", text:\"TX: \" + txTime});\n\n// UPDATE Device\nvar msgDevice = {};\nmsgDevice.topic = \"SELECT Device SET LastHeard = NOW(), CurrentStatus = ? WHERE DeviceId = ?\";\nvar updateDevice = [\n [currentStatus, deviceIdT],\n [currentStatus, deviceIdH],\n [currentStatus, deviceIdP]\n ];\nmsgDevice.payload = [updateDevice];\n\nnode.status({fill:\"green\", shape:\"dot\", text:\"LastHeard: \" + txTime});\nreturn [msgDevice, msgTelemetry];","outputs":2,"noerr":0,"x":340,"y":260,"wires":[["3fb0ae.481f7752","66f6be8c.5d08a8"],["b07c532d.85a23"]]},{"id":"3fb0ae.481f7752","type":"mysql","z":"202ea5e3.ae1b3a","mydb":"bb9bff56.2345e","name":"UPDATE Device","x":520,"y":220,"wires":[[]]},{"id":"b07c532d.85a23","type":"mysql","z":"202ea5e3.ae1b3a","mydb":"bb9bff56.2345e","name":"INSERT Telemetry","x":530,"y":280,"wires":[["e094352c.5e4d28"]]},{"id":"bb9bff56.2345e","type":"MySQLdatabase","z":"","host":"raspbari5.parkcircus.org","port":"3306","db":"ha","tz":""}]

```

The _UPDATE_ statement has been verified to work from the (MariaDB) console command prompt with the same database as follows:

```auto
UPDATE Device SET LastHeard = NOW(), CurrentStatus = 0 WHERE DeviceId = 1;

```

After reading the "docs" I changed the command from _UPDATE_ to _SELECT_ only in the flow. The _INSERT_ works fine and I (in my ignorance) simply adapted it for the subsequent update operation. The _msg_ structure is similar (_topic_ & _payload_) for both sets of operations (as confirmed by **Debug** node). I have a stored procedure to bypass all this but simply for my education (& learning 😀), I would like to understand the erroneous code in my example.

The code snippet for the update operation is as follows:

```auto
var msgDevice = {};
msgDevice.topic = "SELECT Device SET LastHeard = NOW(), CurrentStatus = ? WHERE DeviceId = ?";
var updateDevice = [
    [currentStatus, deviceIdT],
    [currentStatus, deviceIdH],
    [currentStatus, deviceIdP]
    ];
msgDevice.payload = [updateDevice];

```

Please note that I have used the command _UPDATE_ and _SELECT_ successively but the error persisted. The node status is _Error_ for the update operation and it is _OK_ for the insert operation. Also, the code variables have correct values being assembled in the transmission to the **mysql** node as confirmed by viewing a temporary **Debug** node.

I know that there are other nodes that could be used but since I have confined my limited use (in many, many flows) to the **mysql** code, I would prefer to correct my mistake here rather than embark on yet another substitution exercise. Thanks for any advice.

Kind regards.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [19 January 2020 10:38 UTC](https://discourse.nodered.org/t/update-record-from-flow-to-mariadb-database/20556/2 "2020-01-19T10:38:36Z")

</div>

> [@baqwas](#):
>
> After reading the "docs" I changed the command from _UPDATE_ to _SELECT_ only in the flow.

What 'docs'? as far as I know, if you want to update something n the database you need to use an UPDATE statement.