CODEWITHSUNDEEP
sqlite
curlybracket
curlybracket

Reputation: 433

SQLite: Select one row or return NULL when no row satisfies conditions

I want to INSERT OR REPLACE a row specified by 'uuid' column value. There is what I have:

INSERT OR REPLACE INTO feature_layer (id, uuid, feature_collection)
VALUES ((SELECT id FROM feature_layer WHERE uuid=?) , ?, GeomFromGeoJSON(?));

Will this query do what I want, which means update row specified by uuid or insert new one if no such row is present?

Upvotes: 0

Views: 110

Answers (1)

antonio
antonio

Reputation: 18242

  • Assuming that your id field is defined as (with AUTOINCREMENT)

    id INTEGER PRIMARY KEY AUTOINCREMENT

the correct statement will be

INSERT OR REPLACE INTO feature_layer (id, uuid, feature_collection)
VALUES ((SELECT id FROM feature_layer WHERE uuid=?), ?, GeomFromGeoJSON(?));

(you missed one ( in your query)

  • If your id field is defined as (without AUTOINCREMENT)

    id INTEGER PRIMARY KEY

you will want to use COALESCE to set a new id (I have added one ? for the new id) if no records are found:

INSERT OR REPLACE INTO feature_layer (id, uuid, feature_collection)
VALUES (COALESCE((SELECT id FROM feature_layer WHERE uuid=?),?) , ?, GeomFromGeoJSON(?));

With the above queries, as you want, you will update uuid and feature_collection when the id matches the one filtered by uuid, and if no records are found, then it will insert a new record with your desired values for uuid and feature_collection.

Anyway, take into account that your id field must be the PRIMARY KEY to use this method.

Upvotes: 1

Related Questions