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? — Georg Richter
– Georg Richter, Commented Dec 30, 2021 at 6:15

tprommi · Accepted Answer · 2021-12-31 13:06:50Z

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.

Collectives™ on Stack Overflow

SQL insert into if not exists in mariadb

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related