CODEWITHSUNDEEP
sqlpsql
jorgecf
jorgecf

Reputation: 25

Delete a row using the cartesian porduct in SQL

I have a db with two tables:

Flights table:

code start end 
1 Madrid London

Bookings table:

flight_code passport_number price 
1           12345678        100

Second table's "flight_code" references first table's code, since a booking can only be made of an already existing flight on the db.

If I want to check out the passport number of all the people with a flight booked that starts in Madrid I'd do:

SELECT passport_number FROM bookings, flights WHERE flight_code=code AND start = 'Madrid';

And it returns in this case 12345678.

But, what sql order should I use if I want to delete all the bookings (the three fields) that start in Madrid?

To see if the flight starts in Madrid I should check out the fligth_code with the code of the 1st table, like I did with the select code, but I don't see how i could do that with DELETE...

Upvotes: 0

Views: 49

Answers (1)

M.Ali
M.Ali

Reputation: 69574

DELETE FROM Bookings 
WHERE EXISTS (SELECT 1 
              FROM Flights 
              WHERE flight_code = code
               AND start = 'Madrid')

Upvotes: 1

Related Questions