Oracle SQL 查找,删除重复行，有则更新，无则插入

    MERGE INTO CRPROPTREND T1 USING DUAL T2
    ON (T1.URIMD5="abc")
    WHEN MATCHED THEN
    INSERT　VALUES(...)
    WHEN NOT MATCHED THEN
    UPDATE SET ...;
    

 

-- This query is not editable, but generally faster 
Select 
         URIMD5,
         count(*)
from     RUNPPV.CRPROPTREND
group by 
         URIMD5
having   count(*) > 1


-- This query is editable, but generally slower 
Select A1.*, A1.rowid
from RUNPPV.CRPROPTREND A1 
where exists (Select 'x' from RUNPPV.CRPROPTREND A2 
where A1.URIMD5 = A2.URIMD5
and A1.ROWID <> A2.ROWID)
-- To delete all but one of each duplicate row, 
-- change the first line to 'Delete' 
-- and change the '<>' to '>' (keeps first duplicate) 
--                     or '<' (keeps last duplicate)