Mysql INSERT or UPDATE multiple columns on duplicate key

General
1

Hi,
I am new to Node-Red and after a few days of research I got the following flow to work, but it seems complicated to achieve something simple.
I am wondering if there is a better way to do a SQL 'INSERT' with 'UPDATE ON DUPLICATE KEY'... when there are multiple column values to update.

Here is the payload data I need to INSERT / UPDATE:

msg.payload : array[12]
array[12]
[0 … 9]
0: object
date: "2024-12-27"
victron: 12.21
solar: 22.69
production: 8.07

The columns that interest us in the 'energy_forecast' table are:

  • id: Primary key - autoincrement
  • date: unique key (YYY-MM-DD)
  • forecast_solar_victron: float (victron)
  • forecast_solar_sun: float (solar)
  • solar_production: float (production)

For each date I need to write the values for these columns in the table. That happens multiple time each day so sometime a new "date" row needs to be inserted and most of the time it will update an existing row.

It seems that the "INSERT ... UPDATE ON DUPLICATE KEY" only allows to set one column value (am I wrong?)

So to go around it I first do a query to 'INSERT / UPDATE ON DUPLICATE KEY' for the date column. Like that I am sure all the required date rows exist and I can safely later update the other column values in a separate SQL query.

I added some delay before the UPDATE query (to give time for the previous INSERT query to complete (I do not like that approach).

Is there a more elegant way to achieve the same result?

I include the flow, but I cannot find the preview option to check if it come through OK.
Thanks for your input.

[
    {
        "id": "e5b521463590f8f7",
        "type": "tab",
        "label": "Flow 1",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "7845ab5635739ccc",
        "type": "link in",
        "z": "e5b521463590f8f7",
        "name": "link in 23",
        "links": [
            "eea3f4a7f2e7905d",
            "026a5cc4b211ae23",
            "37dc78ab5e14a9ac"
        ],
        "x": 135,
        "y": 160,
        "wires": [
            [
                "b870bd6beddb2813"
            ]
        ]
    },
    {
        "id": "9f2985e6ed2779bb",
        "type": "ui-chart",
        "z": "e5b521463590f8f7",
        "group": "a347c20fc22df49e",
        "name": "Prévision & Production Solaire",
        "label": "Prévision & Production solaire",
        "order": 1,
        "chartType": "bar",
        "category": "[\"production\",\"victron\", \"solar\"]",
        "categoryType": "json",
        "xAxisLabel": "",
        "xAxisProperty": "date",
        "xAxisPropertyType": "property",
        "xAxisType": "category",
        "xAxisFormat": "",
        "xAxisFormatType": "auto",
        "xmin": "",
        "xmax": "",
        "yAxisLabel": "kWh",
        "yAxisProperty": "payload",
        "yAxisPropertyType": "msg",
        "ymin": "0",
        "ymax": "",
        "bins": 10,
        "action": "replace",
        "stackSeries": false,
        "pointShape": "circle",
        "pointRadius": 4,
        "showLegend": true,
        "removeOlder": 1,
        "removeOlderUnit": "3600",
        "removeOlderPoints": "",
        "colors": [
            "#fed60f",
            "#7aa7ff",
            "#8fc9ff",
            "#2ca02c",
            "#a347e1",
            "#d62728",
            "#ff9896",
            "#9467bd",
            "#c5b0d5"
        ],
        "textColor": [
            "#666666"
        ],
        "textColorDefault": true,
        "gridColor": [
            "#e5e5e5"
        ],
        "gridColorDefault": true,
        "width": 6,
        "height": 8,
        "className": "",
        "interpolation": "linear",
        "x": 470,
        "y": 100,
        "wires": [
            []
        ]
    },
    {
        "id": "b870bd6beddb2813",
        "type": "function",
        "z": "e5b521463590f8f7",
        "name": "Sort data",
        "func": "msg.payload = global.get(\"forecastData\", \"file\");\nmsg.payload = msg.payload.sort((a, b) => a.date.localeCompare(b.date));\nreturn msg;",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 240,
        "y": 160,
        "wires": [
            [
                "9f2985e6ed2779bb",
                "2bb4c724a1b7c837",
                "635267c4bba4db96"
            ]
        ]
    },
    {
        "id": "45a8c67919909342",
        "type": "mysql",
        "z": "e5b521463590f8f7",
        "mydb": "7219472b7fcfd297",
        "name": "update",
        "x": 1070,
        "y": 160,
        "wires": [
            []
        ]
    },
    {
        "id": "2bb4c724a1b7c837",
        "type": "split",
        "z": "e5b521463590f8f7",
        "name": "",
        "splt": "\\n",
        "spltType": "str",
        "arraySplt": 1,
        "arraySpltType": "len",
        "stream": false,
        "addname": "",
        "x": 390,
        "y": 200,
        "wires": [
            [
                "619daed239a35dad"
            ]
        ]
    },
    {
        "id": "635267c4bba4db96",
        "type": "debug",
        "z": "e5b521463590f8f7",
        "name": "debug 5",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 440,
        "y": 140,
        "wires": []
    },
    {
        "id": "ceca56ed8d433da3",
        "type": "function",
        "z": "e5b521463590f8f7",
        "name": "UPDATE query",
        "func": "let victron = msg.payload.victron || 0;\nlet solar = msg.payload.solar || 0;\nlet production = msg.payload.production || 0;\n\nmsg.topic = \"UPDATE energy_forecast SET \\\n        forecast_solar_victron = \" + victron + \", \\\n        forecast_solar_sun = \" + solar + \", \\\n        solar_production = \" + production + \" \\\n    WHERE date = '\" + msg.payload.date + \"';\"\n\nreturn msg;",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [
            {
                "var": "mustache",
                "module": "mustache"
            }
        ],
        "x": 900,
        "y": 160,
        "wires": [
            [
                "45a8c67919909342"
            ]
        ]
    },
    {
        "id": "619daed239a35dad",
        "type": "template",
        "z": "e5b521463590f8f7",
        "name": "INSERT query",
        "field": "topic",
        "fieldType": "msg",
        "format": "handlebars",
        "syntax": "mustache",
        "template": "INSERT INTO energy_forecast (date)\nVALUES ('{{payload.date}}')\nON DUPLICATE KEY UPDATE date = '{{payload.date}}';\n",
        "output": "str",
        "x": 540,
        "y": 200,
        "wires": [
            [
                "8c60e40d9b18f13c",
                "f4e68c47c6800bb3"
            ]
        ]
    },
    {
        "id": "8c60e40d9b18f13c",
        "type": "mysql",
        "z": "e5b521463590f8f7",
        "mydb": "7219472b7fcfd297",
        "name": "insert",
        "x": 710,
        "y": 240,
        "wires": [
            []
        ]
    },
    {
        "id": "f4e68c47c6800bb3",
        "type": "delay",
        "z": "e5b521463590f8f7",
        "name": "",
        "pauseType": "delay",
        "timeout": "1",
        "timeoutUnits": "seconds",
        "rate": "1",
        "nbRateUnits": "1",
        "rateUnits": "second",
        "randomFirst": "1",
        "randomLast": "5",
        "randomUnits": "seconds",
        "drop": false,
        "allowrate": false,
        "outputs": 1,
        "x": 720,
        "y": 160,
        "wires": [
            [
                "ceca56ed8d433da3"
            ]
        ]
    },
    {
        "id": "a347c20fc22df49e",
        "type": "ui-group",
        "name": "Forecast",
        "page": "157f9081842c4941",
        "width": "6",
        "height": "1",
        "order": 1,
        "showTitle": true,
        "className": "",
        "visible": "true",
        "disabled": "false",
        "groupType": "default"
    },
    {
        "id": "7219472b7fcfd297",
        "type": "MySQLdatabase",
        "name": "Energy DB",
        "host": "192.168.1.190",
        "port": "3306",
        "db": "energy",
        "tz": "",
        "charset": "UTF8"
    },
    {
        "id": "157f9081842c4941",
        "type": "ui-page",
        "name": "Solaire",
        "ui": "3ca9ca28c624f2cd",
        "path": "/page2",
        "icon": "home",
        "layout": "grid",
        "theme": "7ccb1998bfb0cb9d",
        "breakpoints": [
            {
                "name": "Default",
                "px": "0",
                "cols": "3"
            },
            {
                "name": "Tablet",
                "px": "576",
                "cols": "6"
            },
            {
                "name": "Small Desktop",
                "px": "768",
                "cols": "9"
            },
            {
                "name": "Desktop",
                "px": "1024",
                "cols": "12"
            }
        ],
        "order": 1,
        "className": "",
        "visible": "true",
        "disabled": "false"
    },
    {
        "id": "3ca9ca28c624f2cd",
        "type": "ui-base",
        "name": "UI Name",
        "path": "/dashboard",
        "appIcon": "",
        "includeClientData": true,
        "acceptsClientConfig": [
            "ui-notification",
            "ui-control"
        ],
        "showPathInSidebar": false,
        "showPageTitle": true,
        "navigationStyle": "default",
        "titleBarStyle": "default"
    },
    {
        "id": "7ccb1998bfb0cb9d",
        "type": "ui-theme",
        "name": "Default Theme",
        "colors": {
            "surface": "#ffffff",
            "primary": "#0094CE",
            "bgPage": "#eeeeee",
            "groupBg": "#ffffff",
            "groupOutline": "#cccccc"
        },
        "sizes": {
            "density": "default",
            "pagePadding": "12px",
            "groupGap": "12px",
            "groupBorderRadius": "4px",
            "widgetGap": "12px"
        }
    }
]
2

Yes you are :upside_down_face:

Here is an example query (this is in a template node, hence the mustaches)

insert into espressofill (socketid, percent) 
values ((select max(socketid) from significantevents), {{payload}} )
on duplicate key update
percent = {{payload}},
timestamp = now()
1 Like
3

Thanks, I tried that before but did not work. Must have done something wrong unrelated.
I will try tomorrow and confirm.

4

Well I must admit I don't know for sure if it works, but I have not noticed an error ...

Let me check.

Yup, works.

5

Yes, I just tried it and it works great. :smiley:
I must have done something wrong in previous attempts.

I also preprocessed the data so that I can use a template:

INSERT INTO energy_forecast (
    date,
    forecast_solar_victron,
    forecast_solar_sun,
    solar_production
    )
VALUES (
    '{{payload.date}}',
    '{{payload.victron}}',
    '{{payload.solar}}',
    '{{payload.production}}'
    )
ON DUPLICATE KEY UPDATE 
    forecast_solar_victron = '{{payload.victron}}',
    forecast_solar_sun = '{{payload.solar}}',
    solar_production = '{{payload.production}}'
;
1 Like