Henrik Norberg February 2016

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

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!

Answers


gimbel0893 February 2016

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;


CL. February 2016

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

Post Status

Asked in February 2016
Viewed 2,458 times
Voted 8
Answered 2 times

Search




Leave an answer