Arnica NanoQuery implements CacheDB for edge computing and processes data natively as in databases, using SQL.
Since CacheDB is an instance of embedded SQLite database engine, its syntax rules are applied, which are mostly PostgresSQL compatible syntax.
However, SQL UPDATE requires special attention.
Let's assume we want to update product info in Orders table:
UPDATE orders SET ProductName = p.ProductName, ProductCode = p.ProductCode FROM orders o JOIN products p ON p.ProductID = o.ProductID
This will not work. Unlike in SQLServer, JOINs cannot be used in SQLite.
Better syntax may be:
UPDATE orders o SET ProductName = p.ProductName, ProductCode = p.ProductCode FROM products p
WHERE products p ON p.ProductID = o.ProductID
This is closer, but it has performance issues when the number of records to update is large.
A better performant code variant is the following:
UPDATE orders o SET ProductName = (SELECT p.ProductName FROM products p WHERE p.ProductID = o.ProductID),
ProductCode = (SELECT p.ProductCode FROM products p WHERE p.ProductID = o.ProductID)
It is fast, but with many columns to update the syntax becomes too complex, and performance might gradually degrade as more columns are to be updated.
The ultimate version is:
UPDATE orders o SET (ProductName, ProductCode) = (SELECT p.ProductName, p.ProductCode FROM products p WHERE p.ProductID = o.ProductID)
SQLite allows multiple columns to be updated in the same SET clause from the same subquery. This SQL UPDATE statement is both elegant and fast.