数据库知识学习--DML操作(一)

1.TABLE_payment_info_ext表中主键是id_TABLE_payment_info_ext，其中一列是 id_TABLE_payment_info；现在发现存在一个id_TABLE_payment_info对应多条信息的情况；需要删除其中多余的数据，以保证一个id_TABLE_payment_info对应一个id_TABLE_payment_info_ext，删除重复数据，sql如下：

DELETE FROM TABLE_payment_info_ext e   --删除重复数据
 where e.id_TABLE_payment_info not in
       (SELECT max(ext.id_TABLE_payment_info_ext)
          FROM TABLE_payment_info_ext ext
         where ext.migrate_from = 'np'
         group by ext.id_TABLE_payment_info
        having count(ext.id_TABLE_payment_info) > 1)
  and e.id_TABLE_payment_info in (SELECT max(ext.id_TABLE_payment_info)
          FROM TABLE_payment_info_ext ext
         where ext.migrate_from = 'np'
         group by ext.id_TABLE_payment_info
        having count(ext.id_TABLE_payment_info) > 1);