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);