Deleting where a primary is not linked to a foreign key

Get help on databases - MySQL, Oracle, Access, etc.
Post Reply
SykomantiS
Registered User
Posts: 14085
Joined: 06 Oct 2004, 02:00
Location: Location, Location...
Contact:

Deleting where a primary is not linked to a foreign key

Post by SykomantiS »

Hi all,

As the title says: I have a table with images (it has a primary key and an image column), and several other tables with foreign keys linked to this table.

Now as it happened, I uploaded images to this table more than once, while testing an app, so there are a lot of images in the table which aren't linked to any of the other tables' foreign keys. Basically redundant data.

How do I delete entries from this image table where the primary keys AREN'T linked to foreign keys in other tables?

I've searched around the net, but I can't find anything.
User avatar
Ron2K
Forum Technical Administrator
Posts: 9050
Joined: 04 Jul 2006, 16:45
Location: Upper Hutt, New Zealand
Contact:

Re: Deleting where a primary is not linked to a foreign key

Post by Ron2K »

Code: Select all

DELETE pk_table
WHERE pk_field NOT IN
(
    SELECT DISTINCT fk_field
    FROM fk_table
)
Kia kaha, Kia māia, Kia manawanui.
SykomantiS
Registered User
Posts: 14085
Joined: 06 Oct 2004, 02:00
Location: Location, Location...
Contact:

Re: Deleting where a primary is not linked to a foreign key

Post by SykomantiS »

Thanks Ron, if I want to add more than one table to the NOT IN clause, I can just add them underneath the first select statement?

Edit: it returns 0 rows affected :|

Edit2: Found a solution :)

If anyone wondered:

Code: Select all

use dbName

declare @OutputTable table (RowID int not null)

INSERT INTO @OutputTable (RowID) SELECT idfk1 FROM fkTable1 WHERE idfk1 IS NOT NULL
INSERT INTO @OutputTable (RowID) SELECT idfk2 FROM fkTable2 WHERE idfk2 IS NOT NULL
INSERT INTO @OutputTable (RowID) SELECT idfk3 FROM fkTable3 WHERE idfk3 IS NOT NULL
INSERT INTO @OutputTable (RowID) SELECT idfk4 FROM fkTable4 WHERE idfk4 IS NOT NULL

DELETE FROM pkTable
WHERE pkId NOT IN
(
    SELECT DISTINCT RowId
    FROM @OutputTable
)
I don't know if it's the best way to do this, but it works.
Problem solved
Post Reply