I have a table with two columns (Luggage # and Delivery status) which checks if my luggage was delivered correctly to an airport (refer to image attached below to see table). When the luggage is at the airport at the start of its journey, its status is AIRPORT. When the luggage arrives correctly the status is DELIVERED.

I want to run an SQL query where if my luggage has arrived and is DELIVERED, then I can remove ALL rows with corresponding luggage # (since there will also be rows where the status is AIRPORT).

How exactly would we do this? It's almost like running a pivot.

enter image description here

CodePudding user response：

You could also just use a SELECT DISTINCT statement on Luggage #.

If all luggage names are unique and the only return values are AIRPORT and DELIVERED, and it can only get AIRPORT at initial intake, this would result in only records with AIRPORT remaining.

CodePudding user response：

You can try something like this.

;WITH cte AS
(
    SELECT DISTINCT Luggage_num
    FROM Luggage
    WHERE status = 'DELIVERED'
)

DELETE L
FROM Luggage L
JOIN cte
  ON L.Luggage_num = cte.Luggage_num