Marilou Marilou - 1 month ago 15
SQL Question

How can I sync two SQLite tables using SQL?

I have two tables that both have PhraseId as a key:

Phrase containing
PhraseId, name and score column
PhraseSource containing
PhraseId and name column


The PhraseSource table is populated during an update and it may have less, the same or more rows.

Is there a way I can


  1. Copy new rows into the Phrase table if they exist in PhraseSource but not Phrase

  2. Update rows in the Phrase table where the name is different in PhraseSource and Phrase

  3. Delete rows in the Phrase table where those rows are present in Phrase but not in Phrase source



Note that I need to preserve the score data in the Phrase table.

CL. CL.
Answer

Easy:

DELETE FROM Phrase;
INSERT INTO Phrase SELECT * FROM PhraseSource;

But if the update isn't small, doing it as described is more efficient:

INSERT INTO Phrase
SELECT * FROM PhraseSource
WHERE PhraseId NOT IN (SELECT PhraseId FROM Phrase);

UPDATE Phrase
SET name = (SELECT name
            FROM PhraseSource
            WHERE PhraseId = Phrase.PhraseId)
WHERE name <> (SELECT name
               FROM PhraseSource
               WHERE PhraseId = Phrase.PhraseId);

DELETE FROM Phrase
WHERE PhraseId NOT IN (SELECT PhraseId FROM PhraseSource);