SQL query not working with exec function and sqlite database connection

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!

Answer 1

[Aditya Singh]

Hi Pranav,

To my understanding you are trying to merge two tables based on certain constraints in SQLite.

There is no "MERGE" query in SQLite. The queries supported can be found at Query Language Understood by SQLite.

The merging which you want to achieve can be done by using Joins. For example

CREATE TABLE tc(key,col1,col2)

INSERT INTO tc (key,col1,col2)
SELECT ta.key, ta.col1, tb.col2
FROM ta FULL OUTER JOIN tb USING(key)

More information can be found at join - Combine two tables in SQLite - Stack Overflow.

Hope it helps!

[Pranav]

Hi Aditya - thanks that's really useful.

I am essentially periodically adding data to a database through the merge query.

I was resisting doing a join becuase the database is quite large and i cant read it all into memory. Am I right in assuming that to do a join, both tables would have to be read into MATLAB and occupy memory??

Thanks,

Pranav