Display validation message with duplicate StudentId one by one

Question

I want to check duplicate Id validation in sql server, that validation grid display row number , validation message & duplicate ID from sql server

I am not able to understand that how can I do it ?

Help is really appreciate

Create Table

    CREATE TEMPORARY TABLE IF NOT EXISTS Tbl_Student
    (RowID INT PRIMARY KEY auto_increment, StudentID BIGINT);

Insert Records

Insert into Tbl_Student(RowID,StudentID) values (1,101)
Insert into Tbl_Student(RowID,StudentID) values (2,102)
Insert into Tbl_Student(RowID,StudentID) values (3,101)
Insert into Tbl_Student(RowID,StudentID) values (4,102)
Insert into Tbl_Student(RowID,StudentID) values (5,103)

Please let share if any solution over there

Thank you

@GordonLinoff if any solution available you can share sql server code syntax — shivani, May 29 '19 at 11:15
No prevention not applicable here, I have to display validation message only for studentId — shivani, May 29 '19 at 11:23
Possible duplicate of [Finding duplicate values in a SQL table](https://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table) — Darren Lamb, May 29 '19 at 11:25

score 1 · Accepted Answer · edited May 29 '19 at 11:29

1

Try the following Query,

SELECT RowID, CONCAT('StudentID ',StudentID, ' is Duplicate) AS Error FROM Tbl_Student WHERE StudentID IN (SELECT StudentID FROM Tbl_Student GROUP BY StudentID  HAVING COUNT(*) > 1)

edited May 29 '19 at 11:29

Vahid Farahmandian

6,081
7
42
62

answered May 29 '19 at 11:26

Navas Kurungattil

44
2

score 0 · Answer 2 · answered May 29 '19 at 11:26

Here is T-SQL code, you might need to transform it to MySql format:

Select * from(
      Select RowId, Count(*) Over(Partition By StudentId Order By RowId) as Cnt From [YourTable]
) as K
Where Cnt>1

You can also use Row_Number or Count aggregate function to achieve the same result too.

Here is plenty of MySql solutions for this problem:

Find duplicate records in MySQL

score 0 · Answer 3 · answered May 29 '19 at 11:26

0

To display the duplicates I would do:

select
  RowID, 
  concat('Student ', StudentID, ' is duplicate') as StudentID
from Tbl_Student where StudentID in (
  select StudentID from Tbl_Student group by StudentID having count(*) > 1
)

answered May 29 '19 at 11:26

The Impaler

45,731
9
39
76

score 0 · Answer 4 · answered May 29 '19 at 11:28

You could use a join on the suquery having count of strundet id > 1

  select id,  student_id, concat('Stundent id' , student_id, ' is duplicated')
  from  my_table m
  inner join  (
    select student_id 
    from my_table  
    group by student_id 
    having count(*) > 1  
  ) t on t.student_id  = m.student_id

Display validation message with duplicate StudentId one by one

4 Answers4