Dynamic search in SQL Server using java

Question

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.

Dawood ibn Kareem · Accepted Answer · 2012-01-11 05:17:51Z

1

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

answered Jan 11, 2012 at 5:17

Dawood ibn Kareem

80.2k15 gold badges102 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

samar Over a year ago

Thanks David for your response but I have to update the remark column for the duplicate records except the original one. E.G I have three employee id as 001,001,001 then the second and third record should be updated except the first one. Thanks again in advance

Dawood ibn Kareem Over a year ago

Right, that's a different, and somewhat more difficult question. Is there a column in the table which is guaranteed unique, like some kind of sequential id? If so, you can do this entirely in SQL (which I'll post after you reply). If not, I don't know of any way to do this.

samar Over a year ago

See till now there is no unique key, but if you want then I can add a column like row count but it will be sequential starting from 2 as row no 1 will contain the header. Thanks

Dawood ibn Kareem Over a year ago

OK, so I'd write something like

update employee set remark = 'duplicate' where exists( select 1 from employee a where a.empid = employee.empid and a.empname = employee.empid and a.rowcount < employee.rowcount )

Perception · Accepted Answer · 2012-01-11 05:16:22Z

0

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.

answered Jan 11, 2012 at 5:16

Perception

80.8k19 gold badges190 silver badges197 bronze badges

1 Comment

samar Over a year ago

Sorry for inconvenience, I ama using SQL Server 2005 and I have to run the query through java and I have to update the remark column for the duplicate records except the first record. For example I have 3 employee id as 001, 001, 001 then on the remark column I have to update duplicate in the second and third record.

Fahim Parkar · Accepted Answer · 2012-01-11 05:17:23Z

0

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...

answered Jan 11, 2012 at 5:17

Fahim Parkar

31.8k46 gold badges168 silver badges285 bronze badges

Collectives™ on Stack Overflow

Dynamic search in SQL Server using java

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related