INSERT
To insert data into a collection, you need to specify the fields and values for them. If the collection doesn’t exist already, it will be created. Multiple rows can be inserted at once.
INSERT INTO cities (name, state, population, founded, capital) VALUES
('Wichita', 'Kansas', 395000, 1868, false),
('Rapid City', 'South Dakota', 76000, 1876, false),
('Annapolis', 'Maryland', 40600, 1649, true)
You can set the document ID by providing a value for the field _documentId, otherwise a new UUID will be generated. Jetspike doesn’t check if the document already exists, if you provide an ID of an existing document, it will be overwritten.
INSERT from a SELECT
In some situations, we want to dynamically insert data based on a query from another table. In following example, we create a new record in the collection “offices” for each capital city in our “cities” collection.
INSERT INTO offices (_documentId, location, cityId)
SELECT LEFT(NEWID(), 8), name + ', ' + state, _documentId
FROM cities
WHERE capital = true
After running the code, we can see the new record created in the “offices” collection:
UPDATE
The update statement can be used to modify field values. If the field doesn’t exist in the document already, it will be created. Be aware that not providing a condition will update all documents in the collection.
UPDATE cities
SET population = 40812,
zipCodes = (21401, 21402, 21403, 21404, 21405, 21409, 2141, 21412),
updated = GETDATE()
WHERE name = 'Annapolis'
You can also provide dynamic expressions for updated fields. You can use both existing values of and built-in functions. UPDATE operations can be run over emulated collections to access full functionality of dynamic conditions, for performance considerations see here.
UPDATE emulated.products
SET price = productionCost * 1.2
WHERE price < productionCost * 1.2
DELETE
The DELETE statement is used to delete documents from a collection. Please note that deleted documents can’t be restored. Not providing a condition will result in deleting all documents in a collection.
DELETE FROM cities WHERE population < 5000
Further reading
Now you know how to insert, modify and delete data in your Firestore. There are some further topics you could like to read: