CODEWITHSUNDEEP
sqldatabasesqlite
thpl
thpl

Reputation: 5910

Using JOINS in UPDATE statement when merging in SQLite

I'm currently working with distributed SQLite Databases. Each client has it's own database which he can sync with the server database.

The server receives a database from a client and updates and inserts it's own rows according to the database the client sends.

While inserting new records is as easy as: 

ATTACH DATABASE 'clients_uploaded_db' AS toMerge;
INSERT INTO `presentation` SELECT * FROM toMerge.presentation WHERE id NOT IN (
    SELECT id FROM `presentation`
)

updating is not. I need to check if a client record has changed (presentation.changedate has a smaller value than presentation.changedate in the client db) and update if necessary.

In DBMS such as MySQL the following would be possible. But using joins on UPDATEs in SQLite is not possible.

ATTACH DATABASE 'clients_uploaded_db' AS toMerge;
UPDATE presentation 
INNER JOIN toMerge.presentation AS tp ON
   id = tp.id 
SET
    label = tp.label
WHERE 
    tp.changedate > changedate

I've read through several SO questions but I could only find solutions where only one row needs to be updated or where the id's are known.

DB-Structure

server_db:

presentation (id:pk, label, changedate)

clients_uploaded_db:

presentation (id:pk, label, changedate)

TL;DR

I can't join tables on UPDATE but I need to make rows of a table exactly the same as the rows on a table on another database that is made available in my query, but only when the changedate col of the second database is higher than that of the first one.

What I have done so far

Upvotes: 2

Views: 95

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

SQLite doesn't support a from clause. So you have to do this with correlated subqueries. It is not clear what your update is supposed to be doing. But here is an example

UPDATE presentation 
SET label = (select tp.label
             from toMerge.presentation tp
             where presentation.id = tp.id and
                   tp.changedate > presentation.changedate
             order by tp.change_date
             limit 1);

Upvotes: 1

Related Questions