Eliminate records of one table based on another table record count
Well, based on what I see (or understand...) you are not 'fully' exploiting the concepts of 'relational' database design...... (and
might your 'normalization' of it not be 100%).
It would look like this: Table 1 contains unique values with a related record in table 2. Once you delete a record in table 1, it
could be deleted in table 2 via 'cascading delete' (although that is a bit tricky, so won't recommend it).
In your case I:
1) Can't see why you have 2 tables in first place: if there is a one-on-one relationship, just put them in one table
2) I learn from the example that based on the number of records in T1 (3 * 1111111) you delete the 'oldest\lowest' record in T2:
W00001. But there is no relationship other then the number of records?
Based on this I suggest you look into your database design: try to put it in one table. If that's no option, put in T1 only one
record (1111111), in T2 'subrecords': W00001 and up. Then you only have to delete records from T2.
Jacques
Hi,
How would I go about in completing the following as I can't seem to make it
happen through queries...
I have 2 tables where the first table "Table1" holds the proper record
count, and a second table "Table2" where at times may hold too many records.
Based on "Table1" record count, I basically need to eliminate (or mark) any
excess records in "Table2 going from least to greater.
Table 1
Ref # 1 (Numeric Only)
1111111
1111111
1111111
2222222
2222222
___________________
Table 2
Ref # 1 Ref # 2 (Alpha Numeric)
1111111 W00001 (Based on "Table1" count, this record should be
removed)
1111111 W00002
1111111 W00003
1111111 W00004
2222222 W00005 (Based on "Table1" count, this record should be
removed)
2222222 W00006
2222222 W00007
___________________
Result should be:
1111111 W00002
1111111 W00003
1111111 W00004
2222222 W00006
2222222 W00007
Thanks again,
Norm
