CODEWITHSUNDEEP
sqlsqlite
user1679941
user1679941

Reputation:

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.

Upvotes: 1

Views: 1363

Answers (1)

CL.
CL.

Reputation: 180060

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);

Upvotes: 3

Related Questions