Billy G. Allie wrote:
----------------->>>>
The query you are looking for is:
select a.sym from tableone a
where a.sym not in (select b.sym from tablemany b
where b.sym = a.sym);
This query will run MUCH faster if you create a secondary index for
tablemany (in fact, don't even try it without the index for any significant
number or rows):
create index tablemany_sym on tablemany(sym);
A better solution though, would be to use triggers to update tablemany when
tableone is updated, or to wrap both updates in a single transaction so that
the tableone transaction can be rolled back if tablemany is not updated.
Not knowing what you are attempting to do and how the updates occur prevents
me from going into any more detail than this.
----------------->>>>
Oh yeah it helped... Thanks the query took like 4 seconds. I'm not sure I
totally understand the b.sym = a.sym though.
I was trying to not bore anyone with a in depth description. But since you
asked...
There is company data in each record of the tableone, and daily inputs of
accounting data in tablemany. Now when a company gets added to table one
it's because it's YTD sales went above a certain point. So a record gets
inserted into tableone, and then the accounting data gets inserted into
tablemany sequentially in that order. When any company drops below that
certain point it gets flagged and if it stays there for a certain time, it
gets dropped from the tableone and tablemany. The only constraint I had set
was the "REFERENCES tableone ON UPDATE CASCADE" which adds some continuity,
but not much. Hadn't thought about triggers or really anything.
Application was working fine until I noticed that there was a sym in
tableone that didn't exist in tablemany.
Does that describe it good?
Again thanks for the index and select query.
