The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.
Course Of The Month
11 days, 9 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
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 today
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
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month11 days, 9 hours left to enroll
623 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.