Home Ask Login Register

Developers Planet

Your answer is one click away!

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


Quote of the day: live life