Search code examples
sqllistdelete-row

SQL - Delete rows filtered from a query result

From this excellent post https://stackoverflow.com/a/9755787/9502397 I have understood better now how to Use Join to return a list of results with similar references from different tables.

I have that list as shown in the result after running the Select query

select 
    a.OrderDate,
    c.StaffID,
    c.Activate  
from
    tbl_Order a

join tbl_OrderDetails b
    on a.OrderID = b.OrderID

join tbl_Staff c
    on b.StaffID = c.StaffID

where c.Activate = 'False'

However, how can I use that result list and delete the those rows from tbl_Order?

Solution

  • You could use JOIN in DELETE just the same way as how you would use SELECT. Just ensure that the alias in Delete is from the table from which you would like to delete your data from. 

    DELETE a 
FROM
    tbl_Order a
INNER JOIN tbl_OrderDetails b
    on a.OrderID = b.OrderID
INNER JOIN tbl_Staff c
    on b.StaffID = c.StaffID
WHERE c.Activate = 'False'