PCFormat Forum Archive

Posted: **19 Feb 2010, 10:37**

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.

Posted: **19 Feb 2010, 10:49**

Code: Select all

DELETE pk_table
WHERE pk_field NOT IN
(
    SELECT DISTINCT fk_field
    FROM fk_table
)

Posted: **19 Feb 2010, 11:00**

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

PCFormat Forum Archive

Deleting where a primary is not linked to a foreign key

Deleting where a primary is not linked to a foreign key

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

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