Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

unique12u (TechnicalUser) (OP)
21 Jul 06 23:37
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 (TechnicalUser) (OP)
22 Jul 06 1:46
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!
Helpful Member!  alcyone (Programmer)
22 Jul 06 3:33
Maybe something like this will work:

CODE

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 (TechnicalUser) (OP)
22 Jul 06 11:26
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 (Programmer)
23 Jul 06 5:56
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:

CODE

delete from gl_master
where glm_delete = 'D'
Good luck!
Alcyone
unique12u (TechnicalUser) (OP)
23 Jul 06 11:51
Thanks for the help - I ran UPDATE STATISTICS on the database and query runs just fine now!  Thanks for all the help!

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close