从基于嵌套查询的表中删除SQL [英] SQL-Delete from a table based on a nested query

问题描述

我在mySQL数据库中有三个表，例如：

I have three tables in a mySQL databse as such:

orders: order_sequence_number, contact_id, date_ordered ...

orderdetail: order_sequence_number ...

person: contact_id ...

我正在尝试删除三个表中date_ordered值早于两年的所有行，但是，date_ordered字段仅出现在orders表中，但该表通过order_sequence_number和contact_id字段。

I am trying to delete all rows across the three tables that have a date_ordered value older than two years, however, the date_ordered field only appears in the orders table but the table has links to the other two via the order_sequence_number and contact_id fields.

所以，我的图像有点像这样：...

So, i'd image a bit like this: ...

SELECT * FROM orders WHERE date_ordered BETWEEN X AND Y

然后使用该记录集

DELETE FROM orderdetail WHERE orderdetail.order_sequence_number IN (THE ABOVE)

等人桌...但是没有运气。

Etc for persons table ... but having no luck.

在我看来，就像是某种嵌套的东西子查询，但我遇到困难知道如何进行这样的查询并无法理解它的复杂特性……

Seems to me like some sort of nested subquery but I am having difficulty understanding how to do such a query and cant get my head around the convoluted nature of it...

任何指针都将不胜感激。

Any pointers would be very much appreciated.

推荐答案

DELETE FROM orderdetail 
WHERE order_sequence_number IN 
(
    SELECT id 
    FROM orders 
    WHERE date_ordered BETWEEN X AND Y
)

这篇关于从基于嵌套查询的表中删除SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！