Select subquery problem URGENT Deadline to meet 1

[unique12u]

I have the following two tables

gl_master

glm_account,glm_prft_ctr
10000 1
10000 2
10000 3
10000 4

gl_delete

gld_account,gld_prft_ctr
10000 2
10000 4

I am doing the following query:
SELECT * FROM gl_master
WHERE glm_account in (select gld_account from gl_delete)
AND glm_prft_ctr in (select gld_prft_ctr from gl_delete)

I am getting ALL of the records from gl_master instead of justt the two records that match from gl_delete

Urgent help needed!
TIA
Nique

 

[unique12u]

Ok I can get this select query to work

select *
from gl_master,gl_delete
where gl_master.glm_series = gl_delete.gld_series
and gl_master.glm_account = gl_delete.gld_account
and gl_master.glm_prft_ctr = gl_delete.fld_prft_ctr

but I cannot do the delete query that I need to do from there:

delete
from gl_master
where gl_master.glm_series = gl_delete.gld_series
and gl_master.glm_account = gl_delete.gld_account
and gl_master.glm_prft_ctr = gl_delete.gld_prft_ctr

I get a message that gl_delete not in select

If I do this:
delete
from gl_master,gl_delete
where gl_master.glm_series = gl_delete.gld_series
and gl_master.glm_account = gl_delete.gld_account
and gl_master.glm_prft_ctr = gl_delete.gld_prft_ctr


I get a Syntax error on the ,gl_delete in the FROM statement

PLEASE HELP!

 

[alcyone]

Maybe something like this will work:

delete
from gl_master
where exists (
  select *
  from gl_delete
  where gl_master.glm_account = gl_delete.gld_account
    and gl_master.glm_prft_ctr = gl_delete.lgd_prft_ctr
)

I cannot test it because I have no database installed at my pc, so hope it works.

 

[unique12u]

This seems to work great, but the query is taking a LONG time to run - even with indexes on the tables. Any suggestions to speed the query up?

There are 32000 records in the gl_delete table and of 300,000 in the gl_master table.

Thanks
Nique

 

[alcyone]

Sorry, I don't know if there's a faster method. Is it possible for you to change the table structure? In that case you can add a column to the master table, for example 'glm_delete', which can be used for marking the row to be deleted.
Then the delete query would be very easy, like:

delete from gl_master
where glm_delete = 'D'

Good luck!
Alcyone

 

[unique12u]

Thanks for the help - I ran UPDATE STATISTICS on the database and query runs just fine now! Thanks for all the help!