编写一个查找孤立行的查询,然后使用该查询进行删除。例如

SELECT part_number.id FROM part_number LEFT JOIN manufacturer ON (manufacturer.ID = part_number.manufacturerID) where manufacturer.ID IS NULL

你可以用两种方法。

1使用不存在()

DELETE FROM part_number
 WHERE NOT EXISTS (
           select id from manufacturer
            where part_number.manufacturerID = manufacturer.ID
       )

2。使用临时表(有点难看,但我也会张贴)

-- create a temporary table
CREATE TEMPORARY TABLE `temp_ids`
(
  `id` INTEGER
);

-- store all id's that need to be deleted
INSERT INTO `temp_ids`
SELECT part_number.id FROM part_number LEFT JOIN manufacturer ON (manufacturer.ID = part_number.manufacturerID)
WHERE ISNULL(`manufacturer.ID);

-- delete them
DELETE
FROM part_number
WHERE id IN (
  SELECT `id` FROM `temp_ids`
);

-- drop the table
DROP TEMPORARY TABLE `temp_ids`;

请看我的相关问题: Handling database integrity

希望这对你有用:)