Delete with nested select? 1

[fdarkness]

I have a query that currently SELECTs data that I need to delete. The problem is that I need to delete based on TimeSheetID, AssignmentCode *and* ActivityID. I can't delete based on just one. The main criteria is that I need to delete any entries that have a SUM(Length) = 0. Here's the query that produces exactly what I need to delete, but I have no idea how to actually nest this query into a delete statement itself.

SELECT TimeSheetID, AssignmentCode, ActivityID, SUM(Length) AS Total
FROM Timesheet_TimeLogs
GROUP BY TimeSheetID, AssignmentCode, ActivityID
HAVING SUM(Length) = 0
ORDER BY Total

 

[bborissov]

DELETE FROM Timesheet_TimeLogs
 FROM Timesheet_TimeLogs
INNER JOIN( SELECT TimeSheetID, AssignmentCode, ActivityID
                   FROM Timesheet_TimeLogs
                   GROUP BY TimeSheetID,
                            AssignmentCode,
                            ActivityID
                   HAVING SUM(Length) = 0) Tbl1
ON Timesheet_TimeLogs.TimeSheetID    = Tbl1.TimeSheetID    AND
   Timesheet_TimeLogs.AssignmentCode = Tbl1.AssignmentCode AND
   Timesheet_TimeLogs.ActivityID     = Tbl1.ActivityID

(not tested, so make a veeery good backup first)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[fdarkness]

Woo hoo! It worked perfectly! Thank you! I've now gone from over 800,000 lines in the table to only 98,000. This will do until I can figure out a way to code the application so it doesn't insert lots of junk data in the table in the first place.

(I didn't write it, cause I certainly wouldn't have done it that way!)

Quick question... why two "FROM" stipulations right after each other?

 

[bborissov]

Because the usual DELETE Syntax is: DELETE FROM .... WHERE, If you want to include join query you must add a second FROM. Check BOL for DELETE syntax.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP