SQL Delete similar Entries

Question

SQL Question. I currently have two SQL tables One table called Waitlist and one Called Booking. They are not identical (waitlist contains more information than booking). They appear like so:

Waitlist:

#   Customer    Day                 Timestamp                   Flight
1   Joseph     2017-04-22      2017-04-22 16:20:33.178     F101

Booking:

#   Customer    Flight  Day
1   Timmy   F101    2017-04-22
2   Joseph  F101    2017-04-22

I would like to delete the duplicate data from waitlist (in this case the joseph entry).... I have tried the following: DELETE FROM Waitlist WHERE Customer = booking.customer and day = booking.day and flight = booking.flight I have also tried an INNER JOIN with no luck. Please Help!!

Answer 1

There are multiple ways to achieve your goal but the most appropriate answer to your question would be database vendor specific.

I.e. in both MySQL and SQL Server you can use JOIN directly in DELETE statement

DELETE w 
  FROM waitlist w JOIN booking b 
    ON w.customer = b.customer
   AND w.day = b.day
   AND w.flight = b.flight; 

Sqlfiddle for MySql, Sqlfiddle for SQL Server

In PostgreSQL you can leverage EXISTS

DELETE FROM waitlist w
 WHERE EXISTS (
     SELECT *
       FROM booking
      WHERE customer = w.customer
        AND day = w.day
        AND flight = w.flight
);

SqlFiddle