On Duplicate Key not working in SQLite

Question

In my table, id is the primary key, but this code not working in sqlite3:

insert into text (id,text) VALUES(150574,'Hello') ON DUPLICATE KEY UPDATE 'text' = 'good'

Please help me.

`INSERT .... ON DUPLICATE` don't exist in SqLite. check this link http://stackoverflow.com/questions/2717590/sqlite-upsert-on-duplicate-key-update — bansi, May 13 '14 at 03:56

score 13 · Accepted Answer · answered May 13 '14 at 04:29

13

INSERT .... ON DUPLICATE don't exist in SqLite. But you can use INSERT OR REPLACE to achieve the effect like the following.

INSERT 
    OR REPLACE
INTO
    text (id, text)  
VALUES
    (150574,
        (SELECT
           CASE 
              WHEN exists(SELECT 1  FROM text WHERE id=150574)
              THEN 'good' 
              ELSE 'Hello' 
           END
         )
    )

Ref: http://www.sqlite.org/lang_insert.html

answered May 13 '14 at 04:29

bansi

55,591
6
41
52

a brief explanation of how to use INSERT OR REPLACE would benefit this answer. The link doesn't really mention much about the OR REPLACE part. – Abraham Murciano Benzadon Aug 04 '20 at 15:26

score 2 · Answer 2 · answered Aug 02 '20 at 08:37

2

Since SQLite version 3.24.0 (released 2018-06-04) there is ON CONFLICT support modeled after PostgreSQL. See documentation for more details.

answered Aug 02 '20 at 08:37

Mitar

6,756
5
54
86

On Duplicate Key not working in SQLite

2 Answers2

Linked