SQL-Delete from a table based on a nested query

Question

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 ...

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

Then with that record set

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.

juergen d · Accepted Answer · 2017-01-30 07:15:45Z

6

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

edited Jan 30, 2017 at 7:15

answered Jan 31, 2012 at 11:46

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Diego · Accepted Answer · 2012-01-31 11:45:03Z

2

thats pretty much what you wrote:

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

answered Jan 31, 2012 at 11:45

Diego

36.5k21 gold badges94 silver badges138 bronze badges

Comments

Luc M · Accepted Answer · 2012-01-31 11:54:34Z

1

If you have a big table orders, it's better to use EXISTS

DELETE FROM orderdetail od 
WHERE EXISTS ( SELECT NULL 
               FROM orders o 
               WHERE date_ordered BETWEEN X AND Y
                 and od.order_sequence_number = o.order_sequence_number
             )

answered Jan 31, 2012 at 11:54

Luc M

17.5k26 gold badges78 silver badges91 bronze badges

Collectives™ on Stack Overflow

SQL-Delete from a table based on a nested query

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related