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.
Deleting where a primary is not linked to a foreign key
-
- Registered User
- Posts: 14085
- Joined: 06 Oct 2004, 02:00
- Location: Location, Location...
- Contact:
- 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
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.
-
- 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
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:
I don't know if it's the best way to do this, but it works.
Problem solved
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
)
Problem solved