How do I use UPSERT in sqlite such that created_at time is preserved?-CodePudding

I'm trying to replace this INSERT statement with an UPSERT (now supported in sqlite)

 55     await db.query(
 56       "INSERT OR REPLACE INTO urls(url, title, excerpt, created_at, updated_at) VALUES (?, ?, ?, ?, ?)",
 57       [url, article.title, article.excerpt, new Date().toISOString(), new Date().toISOString()],
 58     );

I want to keep the original created_at timestamp and the other properties (aside from url which is the key) can be updated if the row already exists.

Bonus points if you can also add a count = count 1 column to the table for tracking views.

CodePudding user response：

If there is a unique constraint for the column url then the syntax for UPSERT is:

INSERT INTO urls(url, title, excerpt, created_at, updated_at) VALUES (?, ?, ?, ?, ?)
ON CONFLICT(url) DO UPDATE 
SET title = EXCLUDED.title, 
    excerpt = EXCLUDED.excerpt, 
    updated_at = EXCLUDED.updated_at;

If there is also a column count:

INSERT INTO urls(url, title, excerpt, created_at, updated_at, count) VALUES (?, ?, ?, ?, ?, 1) 
ON CONFLICT(url) DO UPDATE 
SET title = EXCLUDED.title, 
    excerpt = EXCLUDED.excerpt, 
    updated_at = EXCLUDED.updated_at,
    count = count   1;

or if you have defined count with a default value of 1 it can be omitted from the INSERT list:

INSERT INTO urls(url, title, excerpt, created_at, updated_at) VALUES (?, ?, ?, ?, ?) 
ON CONFLICT(url) DO UPDATE 
SET title = EXCLUDED.title, 
    excerpt = EXCLUDED.excerpt, 
    updated_at = EXCLUDED.updated_at,
    count = count   1;