CODEWITHSUNDEEP
javasqlsql-server
samar
samar

Reputation: 41

Dynamic search in SQL Server using java

I have an employee table like:

Empid   EmpName   Remark
001     Bob 
002     Harish  
003     Tom 
004     Dicky   
001     Bob
003     Tom

I have to find the duplicate employee id and accordingly updating the remark field as duplicate !

Thanks.

Upvotes: 0

Views: 263

Answers (3)

Dawood ibn Kareem
Dawood ibn Kareem

Reputation: 79808

update employee set remark = 'duplicate'
where empid in (
  select empid
  from   employee
  group by empid, empname
  having count(*) > 1 )

Upvotes: 1

Fahim Parkar
Fahim Parkar

Reputation: 31631

Below will give you ID of duplicate records

  `select empID, empName, count(empID) as cnt from fschema.myTable group by (empID) having cnt > 1 order by cnt`

Will get back to you how to set remark as 1 for duplicates shortly...

Upvotes: 0

Perception
Perception

Reputation: 80603

This question is very vague, because you do not mention what ORM library you are using or how you are accessing/manipulating your database. But basically want you want to do is execute a derived table query, then make a decision based on the results.

SELECT * FROM
    (SELECT empId, count(empId) numIds from Employee group by empId) IdCount
        WHERE numIds > 1;

Run this query via a PreparedStatement or whatever your ORM framework provides, then iterate over each result and update your remark field.

Upvotes: 0

Related Questions