Update Query

[Steven]

I have a "Table1" with 1 field "Category" with 22 records. The records are
unique.

I have "Table2" with 20 fields and 10,000 records. One of its fields is
"Category".

I want to delete the records in "Table2" where the Category is not found in
"Table1"

I tried some queries but it says "Must be an updateable query".

Can this be done?

Thank you,

Steven

 

[Steven]

This is me again.

I notice it works for what I want if they are both tables.

What if the "Table1" with 1 field "Category" 22 records is actually a query
"Query1" of a table getting the unique "Category" records.

Can it still be used in an update query? Thats when I run into a problem.

Thank you,

Steven

 

[John W. Vinson]

I have a "Table1" with 1 field "Category" with 22 records. The records are
unique.

I have "Table2" with 20 fields and 10,000 records. One of its fields is
"Category".

I want to delete the records in "Table2" where the Category is not found in
"Table1"

I tried some queries but it says "Must be an updateable query".

Can this be done?

Thank you,

Steven

Try a subquery:

DELETE * FROM Table2
WHERE CATEGORY NOT IN(SELECT Category FROM Table1);

 

[John W. Vinson]

What if the "Table1" with 1 field "Category" 22 records is actually a query
"Query1" of a table getting the unique "Category" records.

Can it still be used in an update query? Thats when I run into a problem.

No, because no DISTINCT query is ever updateable.

Perhaps you could post the SQL of the two queries.