Netezza Performance Server

I have duplicate records inserted into Netezza and would like to delete the oldest records.

delete from 

DB_NAME.SCHEMA_NAME.TABLE_NAME  c

where exists ( 

select 

ROWID  

from 

DB_NAME.SCHEMA_NAME.TABLE_NAME  a 

left outer join (

select 

min(ROWID) as min_row

,<....column list>  

from 

DB_NAME.SCHEMA_NAME.TABLE_NAME

group by 

<....column list> 

) x 

on a.ROWID=x.min_row 

where x.min_row is null 

and a.ROWID = c.ROWID

)

;

Obviously also it should go without saying that you need to test this thoroughly in your own environment to ensure it reliably meets your use case before running against any production data - the code sample above has been supplied in good faith, and I do not provide any kind of warranty as to its accuracy (although it has worked reliably for me against many hundreds of thousands of tables in the past).

If you think it would be helpful to be able to iterate this process across large numbers of database objects automatically (as well as automate bi-directional replication between Netezza databases; automate user, group, permission synchronisation with Active Directory/Entra; automate governance/compliance reporting; automate row/column level security; and more), then take a look at:

https://smart-associates.biz/products/smf.php 

Cheers,

Huw Ringer  / Principal Consultant
+44 7768 094 727/  huw@smart-associates.biz

Smart Associates Limited  Office: +44 208 133 6008  / Fax: +44 208 133 6008  
Valley View, The Old Quarry, Haslemere, Surrey GU27 3SS, United Kingdom. Company Registration No: 4804996 VAT No: 811 4279 50
http://www.smart-associates.biz

This e-mail message may contain confidential or legally privileged information and is intended only for the use of the intended recipient(s). Any unauthorized disclosure, dissemination, distribution, copying or the taking of any action in reliance on the information herein is prohibited. E-mails are not secure and cannot be guaranteed to be error free as they can be intercepted, amended, or contain viruses. Anyone who communicates with us by e-mail is deemed to have accepted these risks. Smart Associates Limited is not responsible for errors or omissions in this message and denies any responsibility for any damage arising from the use of e-mail. Any opinion and other statement contained in this message and any attachment are solely those of the author and do not necessarily represent those of the company.