I am trying to insert data into a table using a merge query. The database is a sqlite database with two tables (tmpPriceData and PriceData). I am trying to merge tmpPriceData into PriceData using the below query but I get an error message:
dbfile = 'test.db';
conn = sqlite(dbfile);
mergeQuery= "MERGE PriceData AS target USING tmpPriceData AS source ON target.pDate = source.pDate and target.Symbol = source.Symbol and target.Mnemonic = source.Mnemonic WHEN MATCHED THEN UPDATE SET Value = source.Value WHEN NOT MATCHED by target THEN INSERT (pDate, Symbol, Value, Mnemonic) VALUES (source.pDate, source.Symbol, source.Value, source.Mnemonic)";
exec(conn,mergeQuery)
Error message received:
Error using sqlite/exec
Received exception upon attempting an operation. Exception: [SQL error or missing database test.db. (near "MERGE": syntax error)]. Details: [MERGE PriceData AS target USING tmpPriceData AS source ON target.pDate = source.pDate and target.Symbol = source.Symbol and target.Mnemonic = source.Mnemonic WHEN MATCHED THEN UPDATE SET Value = source.Value WHEN NOT MATCHED by target THEN INSERT (pDate, Symbol, Value, Mnemonic) VALUES (source.pDate, source.Symbol, source.Value, source.Mnemonic)].
Not sure what is going wrong but any help would be much appreciated!
