1 -- UPDATE
 2 UPDATE affair_list
 3 SET deleteState = 0
 4 WHERE gid IN (
 5         SELECT tt.gid
 6         FROM (
 7             SELECT a.gid
 8             FROM affair_list a
 9                 INNER JOIN (
10                     SELECT basicCode, impleCode, orgCode, fullName
11                     FROM `affair_list`
12                     WHERE nodeType IN ('C', 'E')
13                     GROUP BY basicCode, impleCode, orgCode, fullName
14                     HAVING COUNT(1) > 1
15                 ) b
16                 ON (a.basicCode = b.basicCode
17                     AND a.impleCode = b.impleCode
18                     AND a.orgCode = b.orgCode
19                     AND a.fullName = b.fullName)
20             WHERE a.nodeType IN ('C', 'E')
21                 AND gid NOT IN (
22                     SELECT gid
23                     FROM `affair_list`
24                     WHERE nodeType IN ('C', 'E')
25                     GROUP BY basicCode, impleCode, orgCode, fullName
26                     HAVING COUNT(1) > 1
27                 )
28         ) tt
29     );
30 
31 
32 UPDATE affair
33 SET deleteState = 0
34 WHERE affairListId IN (
35         SELECT tt.gid
36         FROM (
37             SELECT a.gid
38             FROM affair_list a
39                 INNER JOIN (
40                     SELECT basicCode, impleCode, orgCode, fullName
41                     FROM `affair_list`
42                     WHERE nodeType IN ('C', 'E')
43                     GROUP BY basicCode, impleCode, orgCode, fullName
44                     HAVING COUNT(1) > 1
45                 ) b
46                 ON (a.basicCode = b.basicCode
47                     AND a.impleCode = b.impleCode
48                     AND a.orgCode = b.orgCode
49                     AND a.fullName = b.fullName)
50             WHERE a.nodeType IN ('C', 'E')
51                 AND gid NOT IN (
52                     SELECT gid
53                     FROM `affair_list`
54                     WHERE nodeType IN ('C', 'E')
55                     GROUP BY basicCode, impleCode, orgCode, fullName
56                     HAVING COUNT(1) > 1
57                 )
58         ) tt
59     );