CODEWITHSUNDEEP
sqlsqlite
Nickon
Nickon

Reputation: 10146

Compare two tables in SQLite

I have two tables in the database MyTable and MyTableChanges. I want to detect changes (which records were inserted/removed from MyTableChanges or edited).

Is it possible? I would like to do this using only SQLite queries.

I know I can use 2 queries like the one below to detect inserted and removed rows:

SELECT DISTINCT SomePrimaryKey
FROM MyTable
WHERE SomePrimaryKey Not IN 
    (SELECT DISTINCT SomePrimaryKey FROM MyTableChanges)

But how to compare changes? Maybe there's some way, dealing with hashes on columns and joins. I don't know how to start. Please, advise me!

EDIT
I know I can do something like this:

CREATE TABLE Test1
(
  MI_PRINX INTEGER PRIMARY KEY AUTOINCREMENT,
  Name CHAR(100),
  Value INTEGER
);

CREATE TABLE Test2
(
  MI_PRINX INTEGER PRIMARY KEY AUTOINCREMENT,
  Name CHAR(100),
  Value INTEGER
);

INSERT INTO Test1 (Name, Value) VALUES('Name1', 1);
INSERT INTO Test1 (Name, Value) VALUES('Name2', 2);
INSERT INTO Test1 (Name, Value) VALUES('Name3', 3);
INSERT INTO Test1 (Name, Value) VALUES('Name4', 4);

INSERT INTO Test2 (Name, Value) VALUES('Name1', 2);
INSERT INTO Test2 (Name, Value) VALUES('Name3', 3);
INSERT INTO Test2 (Name, Value) VALUES('Name4', 5);
INSERT INTO Test2 (Name, Value) VALUES('Name5', 6);
INSERT INTO Test2 (Name, Value) VALUES('Name6', 7);
INSERT INTO Test2 (Name, Value) VALUES('Name7', 8);

-- suppose Name is unique

SELECT * FROM Test1 JOIN Test2 ON Test1.Name = Test2.Name
WHERE Test1.Value <> Test2.Value

But it's kinda uncomfortable solution, because I need to create really long queries for many columns... Any other idea?

Upvotes: 1

Views: 3701

Answers (1)

CL.
CL.

Reputation: 180020

When you use a natural join, the database will join using all columns that have the same name. In this case, this will allow you to compare entire records.

Then, using an outer join, you can find out which records do not have a matching record in the other table:

SELECT Table1.ID
FROM Table1 NATURAL LEFT JOIN Table2
WHERE Table2.ID IS NULL

Upvotes: 1

Related Questions