Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal
Solved
T-SQL help to delete records
Posted on 2012-12-29
I need help coding a t-sql statement to delete certain records from a sql server table. I'm including pictures of some data before the delete and after the delete. I'm currently doing the delete using VBA in Access 2010, but want to change to use a stored procedure.
This is what the data looks like before. The same studentID may have multiple records. I want to save the record with the largest SemesterPS and delete the others. If the student only has one records (eg: studentID = 45628), I do not want to delete that record.
This is what the data should look like after the delete with one exception 35589 is not deleting the correct record.
The delete statement I'm trying to use is
DELETE From DistanceBySemester WHERE SemesterPS Not In
(SELECT MAX(SemesterPS) from DistanceBySemester GROUP BY StudentID HAVING Count(*)>1)
But it is not deleting the records.
I tried using
DELETE From DistanceBySemester WHERE ID Not In
(SELECT MAX(SemesterPS) from DistanceBySemester GROUP BY StudentID HAVING Count(*)>1)
But that deleted all the records.
Thanks, Susan
This is what the data looks like before. The same studentID may have multiple records. I want to save the record with the largest SemesterPS and delete the others. If the student only has one records (eg: studentID = 45628), I do not want to delete that record.
This is what the data should look like after the delete with one exception 35589 is not deleting the correct record.
The delete statement I'm trying to use is
DELETE From DistanceBySemester WHERE SemesterPS Not In
(SELECT MAX(SemesterPS) from DistanceBySemester GROUP BY StudentID HAVING Count(*)>1)
But it is not deleting the records.
I tried using
DELETE From DistanceBySemester WHERE ID Not In
(SELECT MAX(SemesterPS) from DistanceBySemester GROUP BY StudentID HAVING Count(*)>1)
But that deleted all the records.
Thanks, Susan
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2
- +1
5 Comments
Try this.
DELETE
FROM DistanceBySemester T
INNER JOIN (
SELECT MAX(SemesterPS) as MaxValue FROM DistanceBySemester GROUP BY StudentID
having count(*)>1
) A ON A.StudentID = T.StudentID
WHERE A.MaxValue <> T.SemesterPS
DELETE
FROM DistanceBySemester T
INNER JOIN (
SELECT MAX(SemesterPS) as MaxValue FROM DistanceBySemester GROUP BY StudentID
having count(*)>1
) A ON A.StudentID = T.StudentID
WHERE A.MaxValue <> T.SemesterPS
Active 6 days ago
Try this:
SELECT StudentID, MAX(SemesterPS) AS SemesterPS
INTO #tmp
FROM DistanceBySemester
GROUP BY StudentID
DELETE FROM DistanceBySemester
FROM DistanceBySemester LEFT JOIN
#tmp ON DistanceBySemester.StudentID = #tmp.StudentID AND
DistanceBySemester.SemesterPS = #tmp.SemesterPS
WHERE #tmp.SemesterPS IS NULL
DROP TABLE #tmp
matthewspatrick's solution worked great the first time. I received a error when trying to running venk_r solution. Thank you.
Or simply in one query (no points please):
DELETE d
FROM DistanceBySemester d
LEFT JOIN (SELECT StudentID,
MAX(SemesterPS) LargestSemesterPS
FROM DistanceBySemester
GROUP BY StudentID
) x ON d.StudentID = x.StudentID
AND d.SemesterPS = x.LargestSemesterPS
WHERE x.StudentID IS NULL
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Windocks is an independent port of Docker's open source to Windows. This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
- SQL
- Microsoft SQL Server
- AWS
- Azure
- Docker
- *DevOps
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses
Course of the Month9 days, 23 hours left to enroll
722 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.