开发者

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
);
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜