SQL query not working with exec function and sqlite database connection
18 views (last 30 days)
Show older comments
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!
0 Comments
Accepted Answer
Aditya Singh
on 19 Jul 2023
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)
Hope it helps!
More Answers (0)
See Also
Categories
Find more on Database Toolbox in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!