Deleting rows not existing in another table with composite primary key's

Question

I'm using SQLite and are trying to move old rows from a table Students to Students_Old, and copy new rows from Students_Import.

The problem is I have multiple primary keys as this:

CREATE TABLE "Students" (
`LastName`  TEXT NOT NULL,
`FirstName` TEXT NOT NULL,
`BornDate`  TEXT NOT NULL,
`Class` TEXT NOT NULL,
`Photo` TEXT,
`ValidUntil`    CHAR(10),
PRIMARY KEY(LastName,FirstName,BornDate))

All tables have this structure (except Students_Import that's missing Photo and ValidUntil).

So far I have managed to copy the old rows with this:

INSERT INTO Students_Old 
SELECT DISTINCT a.LastName, a.FirstName, a.BornDate, a.Class, a.Photo, a.ValidUntil FROM
Students a LEFT JOIN Students_Import b ON a.LastName =b.LastName AND a.FirstName=b.FirstName AND a.BornDate=b.BornDate WHERE b.LastName is NULL;

And add the new rows with this:

INSERT INTO Students 
SELECT DISTINCT a.LastName, a.FirstName, a.BornDate, a.Class, "", "" FROM 
Students_Import a LEFT JOIN Students b ON a.LastName =b.LastName AND a.FirstName=b.FirstName AND a.BornDate=b.BornDate WHERE b.LastName is NULL

But I can't figure out how to delete old rows in Students (that don't exist in Students_Import).

I have tried a few variants of this:

DELETE FROM Students WHERE (LastName, FirstName, BornDate) IN
(SELECT DISTINCT a.LastName, a.FirstName, a.BornDate, a.Class, a.Photo, a.ValidUntil FROM 
Students a LEFT JOIN Students_Import b ON a.LastName =b.LastName AND a.FirstName=b.FirstName AND a.BornDate=b.BornDate WHERE b.LastName is NULL);

But I only get syntax error or that I can't use it on multiple rows.

I would appreciate any help!

score 1 · Accepted Answer · answered Feb 08 '16 at 19:00

1

IN does not work with multiple columns.

To find rows that do not exist in another table, use NOT EXISTS with a correlated subquery:

DELETE FROM Students
WHERE NOT EXISTS (SELECT 1
                  FROM Students_Import
                  WHERE Students_Import.LastName  = Students.LastName
                    AND Students_Import.FirstName = Students.FirstName
                    AND Students_Import.BornDate  = Students.BornDate);

answered Feb 08 '16 at 19:00

CL.

173,858
17
217
259

Works like a charm! You have saved me a headache another day (too long time since I did SQL programming, 10+ years). Very much, thank you! – Henrik Norberg Feb 09 '16 at 07:39

score -1 · Answer 2 · answered Feb 08 '16 at 18:03

-1

I don't think you can have multiple columns in the IN clause.

How about: DELETE Students FROM Students s LEFT JOIN Students_Import si ON (s.LastName = si.LastName AND s.FirstName = si.FirstName AND s.BornDate = si.BornDate) WHERE s.LastName IS NULL;

answered Feb 08 '16 at 18:03

gimbel0893

382
3
10

@CL. Yes they do...you can join and then decide which table to delete from like above. 1 of many references you can find: http://stackoverflow.com/questions/16481379/how-to-delete-using-inner-join-with-sql-server – gimbel0893 Feb 19 '16 at 16:50
No they don't in SQLite. – CL. Feb 19 '16 at 16:54
@CL oh ok, i'm always thinking mysql. i see the question asks for sqllite. thx – gimbel0893 Feb 19 '16 at 19:35

Deleting rows not existing in another table with composite primary key's

2 Answers2