Delete Query for SQL beginner

[ease20022002]

Hi,

I have a table that has several fields, but for the action I want to perform there are only 2 fields of concern, Field1 and Field2. Field1 is string and contains material designation id's (MCP) and Field2 contains the quantity of the material (Quantity1). What I want to do is delete all Field1 ids that have a Field2 quantity = 0. What I have done so far is design a query from the original table to GROUP BY Field1 and Sum Field2 to find all the Field1 ids that have a total quantity of 0. Now I need to know how I can delete those records all in one query. I hope someone can help me out and the following is what I have done so far.

SELECT MB51YTD_Table.MCP, Sum(MB51YTD_Table.Quantity1) AS SumOfQuantity1
FROM MB51YTD_Table
GROUP BY MB51YTD_Table.MCP
HAVING (((Sum(MB51YTD_Table.Quantity1))=0));

Now to delete, I thought all I had to do was put DELETE at the beginning of the statement, but I don't think it works.

Any help is appreciated.

 

[PCLewis]

Please confirm:

YOu want to DELETE records

OR

UPDATE records by changing the contents of a field based on the contents on another field in the same record?

I take the latter literal intent from what you have typed. By thinking og the correct verb (UPDATE) you should be a lot close to your goal.

Second issue is that when you use GROUP BY, you are telling the database that you do not want to see each individual records. You are saying that you want to summarise the table.

Hence, you cannot turn a SELECT query that has a GROUP BY clause into a DELETE query by changing the verb.

If you want to delete every record in your table where the sum of quantity for the given MCP is 0, then you could do it with a couple of queries, or use a built in aggregate command in Access. You will need to decide the approach you want to take based on where the back end data is stored. In your example, it looks like an Access query builder piece of SQL.

Try this:

DELETE *
FROM MB51YTD_Table
WHERE DSUM("Quantity1","MB51YTD_Table") =0

So this should (untested sorry) delete records where every Quantity1 for the given MCP is 0. If there are 10 records for a particular MCP and 9 have Quantity1=0 and 1 has Quantity1=1 then none of the 10 records will be deleted.

If that's not what you want, then you have to try and re-state what you want so that it is unambiguous.

Cheers

 

[ease20022002]

Thanks, PC, for your time. Yes, I want to delete the records and, yes, there are multiple records for one MCP, so most of the records are -1 or 1 and for example, if for one MCP value there were 10 records with 5 having -1 and 5 having 1, then the total quantity (sum) for that MCP value would be 0. That is how most of the data is appears.

Is it possible to use the GROUP BY, SUM query that I posted previously as a nested query inside a SELECT * from my original table and DELETE only those MCP values that have a total quantity of 0?

Let me know if it is possible to do this all using nested queries, etc.

Thanks in advance

 

[PCLewis]

You can use a query similar to your original query as a sub-query and use the IN clause if you prefer that over the domain aggregate function.

e.g.

DELETE FROM MB51YTD_Table
WHERE MCP IN
(SELECT MCP FROM MB51YTD_Table
GROUP BY MCP HAVING Sum(Quantity1)=0)

That might do it (not sure if Access wants DELETE * FROM rather than the DELETE FROM clause).