重复数据的查找，更新，删除

--所有重复
select *
  from prod_device d
 where d.physical_identifier_code in
       (select t.physical_identifier_code
          from prod_device t
         group by t.physical_identifier_code
        having count(t.physical_identifier_code) > 1);

                       --查询重复的
          select a.operation_id,a.* from pub_operation a
where a.operation_id !=
(
select max(b.operation_id) from pub_operation b
where a.method_name = b.method_name and a.page_id =b.page_id
) and a.page_id='PUB_DEVICE_STRUCTURES';

--删除
            delete from pub_operation a
where a.operation_id !=
(
select max(b.operation_id) from pub_operation b
where a.method_name = b.method_name and
a.page_id = b.page_id
)     and a.page_id='PUB_DEVICE_STRUCTURES';

     --保留一个，其他更新为空  
        update(
  select a.*
    from prod_device a
   where a.device_id !=     
         (select max(b.device_id)
            from prod_device b
           where a.physical_identifier_code = b.physical_identifier_code
          )
     for update) d set d.physical_identifier_code = '';