2

I am working on system, where I have to update a field dupstat for duplicate records based on 4 columns i.e. cname, fname, mname, dob. Also I have to check if eligibility column contains value "No". I have following query to find out duplicate records:

select o.cname, o.fname, o.CRollNo, o.Coll_Code, o.mname, o.dob, oc.dupeCount, o.Eligible, o.dupstat from REGN_Temp o inner join ( SELECT cname, COUNT(*) AS dupeCount, FName, mname, dob FROM REGN_Temp GROUP BY cname, FName, mname,dob HAVING COUNT(*) > 1 ) oc on o.cname = oc.cname and o.fname = oc.FName and o.mname = oc.mname and o.dob=oc.dob and o.Eligible='No' order by cname, fname

Now I want to update column "dupstat=Y" for all the records which are duplicate and having eligibility = "NO"

Improve this question

1 Answer 1

Reset to default
4

You can UPDATE with JOIN directly, just use UPDATE instead of SELECT followed by the SET like this:

UPDATE o SET o.dupstat='Y' from REGN_Temp o inner join ( SELECT cname, COUNT(*) AS dupeCount, FName, mname, dob FROM REGN_Temp GROUP BY cname, FName, mname,dob HAVING COUNT(*) > 1 ) oc on o.cname = oc.cname and o.fname = oc.FName and o.mname = oc.mname and o.dob=oc.dob and o.Eligible='No'
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.