Update and Delete queries

[Guest]

Hello all,

I have a database containing over 1.2 millions records in one table. I have
added two additional tables containing X codes in one and Y codes in the
other. I need to remove from the main table any records that do not have
the Y code listed in the Y table. I also need to remove any record from the
main table that has a match X code in the X table.

To top it off, the main database contains multiple records with the same ID
number. Such records are valid but for the purpose of my study I must only
work with record with the unique ID #.

I have not done any update or delete queries before. I created an
additional column in the main table formatted as yes/no with no as the
default. I tried an update qeurey and got a Not enough memory error.

So my question is---am I going in the right direction. Is there a better
way to do this?

Thanks

Michael

 

[Michel Walsh]

Hi,


If you make a cross join between tables with millions of records each, you
are likely to get not enough space, or not enough second in your life to get
the result.


You can experiment on smaller set, on the other hand.


To delete record in table1 if table1.X is in table2.X, someone can try:


DELETE DISTINCTROW table1.* FROM table1 INNER JOIN table2 ON
table1.x=table2.x


To delete record in table1 if table1.Y is in table2.Y, someone can try:



DELETE DISTINCTROW table1.* FROM table1 LEFT JOIN table2 ON
table1.y=table2.y WHERE table2.y IS NULL



The first query should not create a problem of space, unless table2 list
duplicated values for its X, but again, if you are close to have 2Gig, the
little extra memory required may be so that the total exceed the limit of
2Gig.


None of the query above handle the "duplicate ID" that, I must say, I failed
to understand.




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Hello,

The main table has 1.2 million records, table X has 79 records and the Y
table has 112. In the 1.2 records I have a column with Y and a column with
X listed. If a record has X code and matches X code in the X table I need to
remove that record from 1.2 table. If a record in 1.2 table has a Y code
which does not appear in the Y table I need to remove it from 1.2 table.

To better explain the duplicate ID, the 1.2 table is shipments of product
from shipper to consignees. Some shipments more than one sku or item was
shipped and thus listed in the 1.2 table as separate records. All of the
multiple records for one shipment have the same Shipment ID #. For the
purpose of the study, unfortunately the vendor looking at the data is unable
to deal with multiple records for a shipment, ( i know, why? but not for me
to ask). So, I can only use records with uniquie shipment ID #'s and remove
all records in which more than one has the same shipment ID#.

Hope that explains better.

thanks

Michael



:

 

[Michel Walsh]

Hi,


The delete are the same as I mentioned before. Once the delete are all
completed, use a total query, on what's left, group by on relevant fields,
and LAST on other fields.



SELECT f1, f2, f3, LAST(f4), LAST(f5), LAST(f6)
FROM myTable
GROUP BY f1, f2, f3



would keep no dup (of the triple (f1, f2, f3)), and take values from any
record for fields f4, f5, and f6. I assume you can identify which fields are
making the groups and which ones are just "accessories".


Hoping it may help,
Vanderghast, Access MVP