# Mysql INSERT or UPDATE multiple columns on duplicate key

**URL:** <https://discourse.nodered.org/t/mysql-insert-or-update-multiple-columns-on-duplicate-key/94357>\
**Category:** General\
**Tags:** mysql\
**Created:** [4 January 2025 17:46 UTC](https://discourse.nodered.org/t/mysql-insert-or-update-multiple-columns-on-duplicate-key/94357 "2025-01-04T17:46:24Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![philtao](https://avatars.discourse-cdn.com/v4/letter/p/7993a0/32.png) [@philtao](https://discourse.nodered.org/u/philtao)\
**Post date:** [4 January 2025 17:46 UTC](https://discourse.nodered.org/t/mysql-insert-or-update-multiple-columns-on-duplicate-key/94357/1 "2025-01-04T17:46:24Z")

</div>

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:

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

```auto
[
    {
        "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"
        }
    }
]

```

---

<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:** [4 January 2025 18:29 UTC](https://discourse.nodered.org/t/mysql-insert-or-update-multiple-columns-on-duplicate-key/94357/2 "2025-01-04T18:29:17Z")

</div>

> [@philtao](#):
>
> (am I wrong?)

Yes you are 🙃

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

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

```

---

<div class="post-metadata">

**Author:** ![philtao](https://avatars.discourse-cdn.com/v4/letter/p/7993a0/32.png) [@philtao](https://discourse.nodered.org/u/philtao)\
**Post date:** [4 January 2025 18:43 UTC](https://discourse.nodered.org/t/mysql-insert-or-update-multiple-columns-on-duplicate-key/94357/3 "2025-01-04T18:43:47Z")

</div>

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

---

<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:** [4 January 2025 18:55 UTC](https://discourse.nodered.org/t/mysql-insert-or-update-multiple-columns-on-duplicate-key/94357/4 "2025-01-04T18:55:05Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![philtao](https://avatars.discourse-cdn.com/v4/letter/p/7993a0/32.png) [@philtao](https://discourse.nodered.org/u/philtao)\
**Post date:** [5 January 2025 08:59 UTC](https://discourse.nodered.org/t/mysql-insert-or-update-multiple-columns-on-duplicate-key/94357/5 "2025-01-05T08:59:13Z")

</div>

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

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

```auto
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}}'
;

```

---

<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:** [5 April 2025 08:59 UTC](https://discourse.nodered.org/t/mysql-insert-or-update-multiple-columns-on-duplicate-key/94357/6 "2025-04-05T08:59:57Z")

</div>

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