使用内部连接更新记录

时间：2022-12-22 01:20:41

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:

我正在开发一个系统，在这个系统中，我需要更新一个字段dupstat，以获取基于4列的重复记录，即cname、fname、mname、dob。另外，我还要检查是否有资格栏包含“否”。我有以下查询查找重复记录:

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"

现在我想要更新列"dupstat=Y"对于所有重复且有资格= "NO"的记录

1 个解决方案

#1

4  

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

您可以直接使用JOIN进行更新，只需使用UPDATE代替SELECT，后面跟着如下设置:

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'

#1

4  

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

您可以直接使用JOIN进行更新，只需使用UPDATE代替SELECT，后面跟着如下设置:

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'
标签：

相关文章