MERGE Join in SQLite

I am writing a process to synchronize a table in my SQLite database to the contents of a text file. There may be information in the table that is not in the file, so I have to preserve some of the data. Specifically, what I need to achieve is:

1. Any rows that are in the table but are no longer in the text file must be deleted from the table.


2. Any rows that are in the text file but are not in the table must be added to the table.


3. Any rows that are in the table and in the text file must be updated.

Here's the structure of the table:

CREATE TABLE [Plates] (
  [PlateRowId]    INTEGER   NOT NULL PRIMARY KEY,
  [PlateId]       GUID      NOT NULL,
  [ListId]        GUID      NOT NULL,
  [CountryId]     TEXT      NULL,
  [LocaleCode]    TEXT      NULL,
  [Plate]         TEXT      NULL,      
  [Notes]         TEXT      NULL,
  [OfficerNotes]  TEXT      NULL,
  [CreatedDate]   DATETIME  NOT NULL
);

CREATE INDEX [IX_ListDetails_Plate] ON [ListDetails] ( [Plate]  ASC );

What I've do so far is to ATTACH a working database, create a table in it with a similar structure, and import all of the data from the file into that table. Here's what that table looks like:

CREATE TABLE import.[ImportedPlates] (
  [ImportedRowId]  INTEGER   NOT NULL PRIMARY KEY,
  [CountryId]      TEXT      NULL,
  [LocaleCode]     TEXT      NULL,
  [Plate]          TEXT      NULL,
  [Notes]          TEXT      NULL
);

After importing the data, I create an index on this table:

CREATE INDEX import.[IX_ImportedPlates_Plate] ON [ImportedPlates] ( [Plate] );

Next, I want to do a "MERGE JOIN" on the two tables to produce a third table that will contain:

• One row for every row in the ImportedPlates table that has a value in the Plate column that is not in the same column in the Plates table.


• One row for every row in the Plates table that has a value in the Plate column that is not in the same column in the ImportedPlates table.


• One row for every row that the two tables have the same Plate in common.

Once I have that, I can iterate over the results and perform the necessary operations to sync the Plates table.

Here's the query I've tried to generate this table, but I have too many rows in it, and I'm not sure that it has all of the data it's supposed to have.

CREATE TABLE import.[MergedData] AS
SELECT [ImportedRowId],
       i.[CountryId],
       i.[LocaleCode],
       i.[Plate] AS [ImportedPlate],
       i.[Notes],
       [PlateRowId],
       d.[Plate] AS [ExistingPlate],
       d.[PlateId],
       d.[CreatedDate],
       d.[OfficerNotes]
FROM main.[Plates] AS d
LEFT OUTER JOIN import.[ImportedPlates] AS i ON d.[Plate] = i.[Plate]
WHERE d.[ListId] IS NULL OR d.[ListId] = @ListId
UNION ALL
SELECT [ImportedRowId],
       i.[CountryId],
       i.[LocaleCode],
       i.[Plate] AS [ImportedPlate],
       i.[HashedPlate],
       i.[Notes],
       [PlateRowId],
       d.[Plate] AS [ExistingPlate],
       d.[PlateId],
       d.[CreatedDate],
       d.[OfficerNotes]
FROM import.[ImportedPlates] AS i INDEXED BY [IX_ImportedPlates_Plate]
LEFT OUTER JOIN main.[Plates] AS d ON i.[Plate] = d.[Plate]
WHERE d.[ListId] IS NULL OR d.[ListId] = @ListId
ORDER BY i.[Plate], d.[Plate]

The other thing about this query is that it returns every row that has a Plate that is common between the two tables. It'd be nice, though not required, if the query would only return rows where one of the other columns was different, so that I only have to update rows that really need updating.

Can someone show me how to fix my query so I get the results I need?