• 0

Optimize SQL statment


Question

Hi all,

assume I have a table containing the following fields:

- airline

- flight number

- from

- to

sample entries maybe:

LH | 1 | london | new york

LH | 2 | chicago| toronto

AA | 1 | miami | vancouver

AA | 2 | LA | Seatle

I want to delete all entries for airlines that have flight leaving from london (i.e. in our example that would be just LH), the current way is using one select statment to get a list of all airlines that fit the criteria then go through the list (loop) and for each issue a DELETE statment. this makes it a bit slow since your hitting the database multiple times. I tried Delete where airline in (select ....) but it won't let me saying that the nested select must be accessing another table.

Anybody has any ideas.

thanks,

Link to comment
Share on other sites

4 answers to this question

Recommended Posts

  • 0

Assuming you are using MySQL, this will work. If not, mod it as needed for your DBMS. As long as your tables are properly indexed and have a primary key, this should be extremely fast:

DROP TEMPORARY TABLE IF EXISTS flights;
CREATE TEMPORARY TABLE flights 
(
  id int not null, 
  airline text, 
  flight_number int, 
  flight_from text, 
  flight_to text, 
  primary key(id)
);

INSERT INTO flights 
(id, airline, flight_number, flight_from, flight_to) 
VALUES 
(1, 'LH','1','london','new york'),
(2, 'LH','2','chicago','toronto'),
(3, 'AA','1','miami','vancouver'),
(4, 'AA','2','LA','Seatle');

DROP TEMPORARY TABLE IF EXISTS temp_flights;
CREATE TEMPORARY TABLE temp_flights 
SELECT * 
FROM flights 
WHERE flight_from = 'london';

DELETE flights 
FROM flights 
JOIN temp_flights 
USING (id);

Link to comment
Share on other sites

  • 0
Hi all,

assume I have a table containing the following fields:

- airline

- flight number

- from

- to

sample entries maybe:

LH | 1 | london | new york

LH | 2 | chicago| toronto

AA | 1 | miami | vancouver

AA | 2 | LA | Seatle

I want to delete all entries for airlines that have flight leaving from london (i.e. in our example that would be just LH), the current way is using one select statment to get a list of all airlines that fit the criteria then go through the list (loop) and for each issue a DELETE statment. this makes it a bit slow since your hitting the database multiple times. I tried Delete where airline in (select ....) but it won't let me saying that the nested select must be accessing another table.

Uhm, if there's only one table and you know what rows you want gone based on a value of one column, you simply use:

DELETE FROM [TABLE_NAME_HERE] WHERE FROM = 'LONDON'

At least in SQL this is how it's done.

Link to comment
Share on other sites

  • 0
Uhm, if there's only one table and you know what rows you want gone based on a value of one column, you simply use:

DELETE FROM [TABLE_NAME_HERE] WHERE FROM = 'LONDON'

At least in SQL this is how it's done.

Yeah Rohde is right basically write a select statement and fine tune it till you are only getting records you want to delete. Then Change the Select ... From to Delete From ....

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.