Hey guys I'm in the process of moving my tables and queries from mysql to the internal SQLITE db inside an andorid app.
When I copied my delete query into my DAO its throwing an error:
DELETE moodBeforeTable, moodAfterTable
FROM moodBeforeTable
JOIN moodAfterTable
ON moodAfterTable.cbtId = moodBeforeTable.cbtId
WHERE moodBeforeTable.cbtId =: cbtId";
The very first word moodBeforeTable, is red underlined and the IDE states its expecting FROM. It doesn't appear to like the mysql syntax. I'm not sure how to rewrite this query. I also have a second delete from another table. Is it possible to execute all these deletes with one method call?
Basically when the user clicks the delete button it needs to delete the record from three separate tables with one click. Can we UNION queries together inside the DAO? If so could someone please give me a simple example of how to write this as the syntax is different to what I'm used to
From conducting further research I have concluded that sqlite does not allow delete queries from mutiple tables at the same time.
To solve this I simply set up three separate delete queries to target each of the tables I wanted to delete data from. When the user clicks the delete button all 3 delete queries in the DAO are executed in the AsyncTask. I thoroughly tested this and it works perfectly.
I hope this answer will help someone else in the future.