I'm trying to update a value of document created on cosmosDB azure using sql api. The problem is that the requests update/ delete/ Insert don't work.
client.queryDocuments(
collectionUrl,
UPDATE tableC
SET prev = 12
WHERE condition
).toArray((err, results) => {
if (err) res.json({ 'A': 12 });
else {
res.json({ 'A': 15});
}
})
CosmosDB SQL is not ANSI SQL implementation. It supports just querying in a somewhat similar manner, but it's not the same thing.
From Introduction to Azure Cosmos DB: SQL API:
Azure Cosmos DB supports querying documents using a SQL language, which is rooted in the JavaScript type system, and expressions with support for relational, hierarchical, and spatial queries. The Azure Cosmos DB query language is a simple yet powerful interface to query JSON documents. The language supports a subset of ANSI SQL grammar and adds deep integration of JavaScript object, arrays, object construction, and function invocation.
So basically CosmosDB takes some syntax rules and conventions from SQL but when you look closer it's another beast. Ansi SQL contains things CosmosDB SQL API does not have and cosmosDB SQL has things ANSI SQL does not have.
Not having support for entire ANSI SQL makes sense, as SQL was designed for relational data manipluation needs. CosmosDB is not a relational database and its change model is working with documents as the changeable units, not individual fields or sets. In DoucmentDB you add one entire document, update one entire document, or delete one entire document. As long as this stands, it does not need the complexity of traditional SQL insert/update/delete syntax.
CosmosDB's flavor of SQL only supports querying.
You cannot use data manipulation SQL.
