How to delete records from a SQL Server database
I have 3 tables:
Files (primary key = FileID)
Accounts (primary key = AccountID)
AccFiles (2 fields: FileID and AccountID)
The AccFiles
table links the primary keys of the Files
table to the Accounts
table.
In my scenario, if 开发者_JAVA百科there is an Account record there will also be 1 corresponding File in the Files table and a reference to both in the AccFiles
table. I guess this is a 1 to 1 relationship. Unfortunately due to a bug some files got deleted but their Account and the record in the AccFiles table didn’t. I need to write a SQL script that will delete records from the Accounts
table and AccFiles
table where there is no longer a corresponding File (as referenced by the Accfiles FileID
).
I’m using SQL Server and have no idea how to go about doing this. Can anyone help?
This will delete all records from AccFiles
whose FileId
field does not exist in the File
table then delete all records from Accounts
whose AccountId
field does not exist in the AccFiles
table:
DELETE AccFiles
WHERE FileID
NOT IN
(
SELECT FileID
FROM Files
);
DELETE Accounts
WHERE AccountId
NOT IN
(
SELECT AccountId
FROM AccFiles
);
精彩评论