SQL insert into if not exists in mariadb

Question

I would like to increment a value in SQL (mariadb) if an entry already exists for this date. Otherwise a new entry should be inserted with a new date.

I have tried a few things, but keep getting syntax errors. I don't know anything about SQL.

The goal is to implement an energy counter. The command should be sent via nodered.

If Not Exists(select * from GAS_daily where date='2021-12-28 00:00:00')
    INSERT INTO GAS_daily(date,value) VALUES('2021-12-28 00:00:00', 1)
ELSE
    update GAS_daily set value=value+1, date=date WHERE date='2021-12-28 00:00:00'

Does this answer your question? [How can I do 'insert if not exists' in MySQL?](https://stackoverflow.com/questions/1361340/how-can-i-do-insert-if-not-exists-in-mysql) — Georg Richter, Dec 30 '21 at 06:15

tprommi · Answer 1 · 2021-12-31T13:06:50.983

I have now found a way myself. I do not know why the IF query did not work. I have solved the problem with DUPLCATE KEY. For this I had to change the table. I now use the timestamp as primary key.

    CREATE TABLE gas_daily (
        ts TIMESTAMP NOT NULL,
        value DECIMAL(10,3) NOT NULL,
        PRIMARY KEY(ts)
    )
    ENGINE=InnoDB
    DEFAULT CHARSET=utf8mb4
    COLLATE=utf8mb4_general_ci;

Add few fake values

    INSERT INTO gas_daily (ts,value) VALUES ('"2021-12-28 00:00:00"',123.0);
    INSERT INTO gas_daily (ts,value) VALUES ('"2021-12-29 00:00:00"',456.0);

    ts                 |value  |
    -------------------+-------+
    2021-12-28 00:00:00|123.000|
    2021-12-29 00:00:00|456.000|

Test 1 - Increases an existing entry

    INSERT INTO gas_daily (ts, value)
    VALUES ("2021-12-29 00:00:00", "3")
    ON DUPLICATE KEY UPDATE
       ts = ts, 
       value = value+3
    ;

    ts                 |value  |
    -------------------+-------+
    2021-12-28 00:00:00|123.000|
    2021-12-29 00:00:00|459.000|

Test 2 - Generates a new value

    INSERT INTO gas_daily (ts, value)
    VALUES ("2021-12-30 00:00:00", "3")
    ON DUPLICATE KEY UPDATE
       ts = ts, 
       value = value+3
    ;

    ts                 |value  |
    -------------------+-------+
    2021-12-28 00:00:00|123.000|
    2021-12-29 00:00:00|459.000|
    2021-12-30 00:00:00|  3.000|

Create unique timestamps in javascript for specific measurement intervals.

    function createSQLCmd(TblName, interval, value){
        ts = new Date(Date.now())
        switch(interval){
            case "Min":
                ts.setSeconds(0);
                TblName=TblName+"_perMinute";
                break;
            case "Min10":
                ts.setSeconds(0);
                ts.setMinutes(ts.getMinutes()-ts.getMinutes()%10);
                TblName=TblName+"_per10Minutes";
                break;
            case "Hour":
                ts.setSeconds(0);
                ts.setMinutes(0);
                TblName=TblName+"_hourly";
                break;
            case "Day":
                ts.setSeconds(0);
                ts.setMinutes(0);
                ts.setUTCHours(0);
                TblName=TblName+"_daily";
                break;
            case "Week":
                ts.setSeconds(0);
                ts.setMinutes(0);
                ts.setUTCHours(0);
                ts.setDate(ts.getDate() - ts.getDay());
                TblName=TblName+"_weekly";
                break;
            case "Month":
                ts.setSeconds(0);
                ts.setMinutes(0);
                ts.setUTCHours(0);
                ts.setDate(1);
                TblName=TblName+"_monthly"; 
                break;
            case "Year":
                ts.setSeconds(0);
                ts.setMinutes(0);
                ts.setUTCHours(0);
                ts.setDate(1);
                ts.setMonth(1);
                TblName=TblName+"_yearly";
                break;
        }
        let sqlTS=ts.toISOString().slice(0, 19).replace('T', ' ');
        return "INSERT INTO "+TblName+" (ts, value) VALUES ('"+sqlTS+"','"+value+"') ON DUPLICATE KEY UPDATE ts = ts, value = value+'"+value+"';"
    }
    
    
    
    console.log(createSQLCmd("GAS","Day",3.4));
    // INSERT INTO gas_daily (ts, value) VALUES ('2021-12-31 00:00:00','3.4') ON DUPLICATE KEY UPDATE ts = ts, value = value+'3.4';

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Dec 31 '21 at 13:59

SQL insert into if not exists in mariadb

1 Answers1