×

Welcome to TagMyCode

Please login or create account to add a snippet.
0
0
 
0
Language: SQL
Posted by: Gerrit Viljoen
Added: May 12, 2014 7:35 AM
Modified: Oct 2, 2014 7:20 AM
Views: 23
Remove duplicate records from a table. Table requires a primary key.

Script is for H2 on Aqua Data.
  1. CREATE TABLE dedupe (
  2.     pid int8 PRIMARY KEY
  3. )
  4. GO
  5.  
  6. INSERT INTO dedupe
  7. SELECT MIN(your_pid)
  8. FROM your_table
  9. GROUP BY your_columns
  10. GO
  11.  
  12. DELETE FROM your_table
  13. WHERE NOT EXISTS (
  14.     SELECT pid
  15.     FROM dedupe
  16.     WHERE dedupe.pid = your_table.your_pid
  17. )
  18. GO
  19.  
  20. DROP TABLE dedupe
  21. GO