replace exist record

[gushenghua]

Is it possible to replace exist records when call python API:

cursor.insert(table_name, data_dict)

[gushenghua]

How can I do it with SQL If insert can not do this (exist then update otherwise insert)?

[Mytherin]

MonetDB does not have native support for UPSERT functionality. However, you can emulate upserts with a procedure. You just have to get the types/keys right. Here is a simple example:

CREATE TABLE integers(id INTEGER PRIMARY KEY, i INTEGER);

CREATE PROCEDURE upsert(_id INTEGER, _i INTEGER)
BEGIN
    IF (SELECT id FROM integers WHERE id=_id) THEN
        UPDATE integers SET i=_i WHERE id=_id;
    ELSE
        INSERT INTO integers VALUES (_id, _i);
    END IF;
END;

-- insert value (id=1, i=10) into integers
CALL upsert(1, 10);
-- update value with id=1 to i=100
CALL upsert(1, 100);