要从多张表中删除数据(即使其中一张表可能没有匹配项),你可以使用以下几种方法,具体取决于你使用的数据库系统:
DELETE t1, t2
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.table1_id
WHERE t1.some_condition = 'value';
在这个例子中,即使 table2
中没有匹配的记录,table1
中的记录仍然会被删除。
DELETE t1
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.table1_id
WHERE t1.some_condition = 'value';
DELETE t2
FROM table2 t2
INNER JOIN table1 t1 ON t1.id = t2.table1_id
WHERE t1.some_condition = 'value';
BEGIN TRANSACTION;
DELETE FROM table1 WHERE some_condition = 'value';
DELETE FROM table2 WHERE table1_id IN (SELECT id FROM table1 WHERE some_condition = 'value');
COMMIT;
如果你可以修改表结构,设置外键约束的 ON DELETE CASCADE 选项:
ALTER TABLE table2
ADD CONSTRAINT fk_table1
FOREIGN KEY (table1_id) REFERENCES table1(id)
ON DELETE CASCADE;
然后只需删除主表记录,相关表记录会自动删除:
DELETE FROM table1 WHERE some_condition = 'value';
哪种方法最适合你取决于你的具体数据库系统和表结构关系。