mass delete with Group By criteria

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

mass delete with Group By criteria

Author

Topic

McBeef
Starting Member

14 Posts

Posted - 2011-05-05 : 15:22:51

I have a transaction log table called WORKDONE that consists of the columns SERIALNUMBER, WORKDONE, and DATESTAMP. I also made a "group by" query of WORKDONE that groups and does a count for each combination of those three. The query is called qryDailyCount and here is the SQL for the query (FirstWord is a function that just takes the first word, in this case the date portion, ignoring the time):

SELECT SERIALNUMBER, WORKDONE, dbo.FirstWord(DATESTAMP) AS DATESTAMP, COUNT(*) AS QTY
FROM dbo.WORKDONE
GROUP BY SERIALNUMBER, WORKDONE, dbo.FirstWord(DATESTAMP)

What I'd like to do is have a SQL statement that would a the records from WORKDONE based on the QTY from qryDailyCount. For example to delete all records from WORKDONE if their qryDailyCount QTY > 4. To restate if a serial number has more than 4 records of a given work on a given day, then all records of that serial-work-day combination will be deleted. It would be possible to look at the results of qryDailyCount and write a number of delete statements based on that, but it would be lot easier if there way to accomplish the same automatically with a single statement.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-05 : 15:27:36

DELETE FROM Workdone w

INNER JOIN
(SELECT SERIALNUMBER, WORKDONE, dbo.FirstWord(DATESTAMP) AS DATESTAMP, COUNT(*) AS QTY
FROM dbo.WORKDONE
GROUP BY SERIALNUMBER, WORKDONE, dbo.FirstWord(DATESTAMP)
HAVING COUNT(*) > 4
) t on w.serialnumber = t.serialnumber

Jim

Test this a select statement first!

Everyday I learn something that somebody else already knew

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-05-05 : 15:32:00

quote:
Originally posted by jimf

DELETE FROM Workdone w

INNER JOIN
(SELECT SERIALNUMBER, WORKDONE, dbo.FirstWord(DATESTAMP) AS DATESTAMP, COUNT(*) AS QTY
FROM dbo.WORKDONE
GROUP BY SERIALNUMBER, WORKDONE, dbo.FirstWord(DATESTAMP)
HAVING COUNT(*) > 4
) t on w.serialnumber = t.serialnumber

Jim

Test this a select statement first!

Everyday I learn something that somebody else already knew

Is your DELETE statement correct?
But seems like folowing is correct:


DELETE FROM w
FROM dbo.WORKDONE w
INNER JOIN
(SELECT SERIALNUMBER, WORKDONE, dbo.FirstWord(DATESTAMP) AS DATESTAMP, COUNT(*) AS QTY
FROM dbo.WORKDONE
GROUP BY SERIALNUMBER, WORKDONE, dbo.FirstWord(DATESTAMP)
HAVING COUNT(*) > 4
) t on w.serialnumber = t.serialnumber

______________________

McBeef
Starting Member

14 Posts

Posted - 2011-05-05 : 17:03:18

I tried the delete statement. It deletes all records for those serial numbers and I wanted to only delete those records with that serial-work-date combination. For example, here is qryCountDay results for serial # 54321:

54321 43" CHANNEL 3/14/2011 154322
54321 CORNER 3/14/2011 4
54321 B PANEL 3/14/2011 2
54321 SLIDE 3/14/2011 2
54321 19" CHANNEL 3/14/2011 4
54321 Re-Weld 4/12/2011 1

after I run the delete, all 54321 records are deleted from the system, good and bad alike. Only want to delete 54321 records that 43" CHANNEL on 3/13/2011 (for which there 154322 records in WORKDONE). I want to leave the remaining good 54321 records untouched.

McBeef
Starting Member

14 Posts

Posted - 2011-05-05 : 17:12:54

This appears to work:

DELETE FROM w
FROM dbo.WORKDONE w
INNER JOIN
(SELECT SERIALNUMBER, WORKDONE, dbo.FirstWord(DATESTAMP) AS DATESTAMP, COUNT(*) AS QTY
FROM dbo.WORKDONE
GROUP BY SERIALNUMBER, WORKDONE, dbo.FirstWord(DATESTAMP)
HAVING COUNT(*) > 4
) t on w.SERIALNUMBER = t.SERIALNUMBER and w.WORKDONE = t.WORKDONE and dbo.FirstWord(w.DATESTAMP) = t.DATESTAMP

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-05 : 17:15:56

Join on whichever criteria you want to delete on. For example, if you wanted to delete regardless of the date, but only those that match workdone, then in the outer join, use

 )t on w.SERIALNUMBER = t.SERIALNUMBER and w.WORKDONE = t.WORKDONE

If you want to delete only those that match the serial number, workdone AND date, then join on all three as in

) t on w.SERIALNUMBER = t.SERIALNUMBER and w.WORKDONE = t.WORKDONE and dbo.FirstWord(w.DATESTAMP) = t.DATESTAMP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources