emulating UPSERT on VIRTUAL table
(1.2) By punkish on 2021-12-26 12:12:54 edited from 1.1 [source]
I realize that UPSERTs are not supported on VIRTUAL tables (and neither are UNIQUE constraints). Nonetheless, I want to avoid duplicate fooid
and want to update a row if it already exists or insert if it doesn't. Consider
CREATE VIRTUAL TABLE IF NOT EXISTS vfoo USING FTS5(fooid, footext)
Of course, I could check for the existence of a row, and then do an INSERT or an UPDATE as required, but is there a more elegant, one-step way to emulate an UPSERT on a virtual table (fts5
as well as rtree
and geopoly
)?