Tuesday, January 28, 2014

Deduping rows in Netezza / Deleting rows in Netezza.

netezza has a hidden rowid for every row in all tables. to dedup, you can do this:

delete from omszip_stage
where rowid in (
select  max(rowid) as rowid
from OMSZIP_STAGE
group by couponid, zip, pid
having count(*) > 1)


8 comments:

  1. rowid is a hidden column in all netezza tables

    ReplyDelete
  2. This comment has been removed by a blog administrator.

    ReplyDelete
  3. My favorite query for finding/deleting duplicates is using rowid:

    select *
    --delete
    from table_with_duplicate t_remove_this
    where exists (select 1 from table_with_duplicate t_keep_this
    where t_remove_this.keyfield1 = t_keep_this.keyfield1
    and t_remove_this.keyfield2 = t_keep_this.keyfield2
    and t_remove_this.keyfield3 = t_keep_this.keyfield3
    and t_remove_this.keyfield1.rowid > t_keep_this.rowid)

    ReplyDelete
    Replies
    1. last line must be
      and t_remove_this.rowid > t_keep_this.rowid)

      Delete
  4. This comment has been removed by a blog administrator.

    ReplyDelete
  5. This comment has been removed by a blog administrator.

    ReplyDelete
  6. NETEZZA Online Training, ONLINE TRAINING – IT SUPPORT – CORPORATE TRAINING http://www.21cssindia.com/courses/netezza-developer-online-training-21.html The 21st Century Software Solutions of India offers one of the Largest conglomerations of Software Training, If you’re serious about a career in IT, 21st Century would like to provide you a guidance don’t hesitate to organize a free demo session. For any further information regarding the courses once go through our website Visit: http://www.21cssindia.com | Call Us +917386622889 - +919000444287 - contact@21cssindia.com

    ReplyDelete
  7. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in NETEZZA TRAINING, kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor led training on TECHNOLOGY. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us.
    Sangita Mohanty
    MaxMunus
    E-mail: sangita@maxmunus.com
    Skype id: training_maxmunus
    Ph:(0) 9738075708 / 080 - 41103383
    http://www.maxmunus.com/

    ReplyDelete