What’s the best way to dedupe a table?

SELECT DISTINCT <insert all columns but the PK here> FROM foo. Create a temp table using that query (the syntax varies by RDBMS but there’s typically a SELECT … INTO or CREATE TABLE AS pattern available), then blow away the old table and pump the data from the temp table back into it.

Related Posts:

  1. What’s the best way to search for a UPC code in a Database?
  2. When should I use CROSS APPLY over INNER JOIN?
  3. Finding duplicate values in a SQL table
  4. Count(*) vs Count(1) – SQL Server
  5. Remove duplicate rows in MySQL
  6. Best way to test if a row exists in a MySQL table
  7. MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
  8. WP Optimization: Removing Orphaned wp_options (especially the autoload ones)
  9. Debugging WordPress
  10. This SQL request call all time and overload my server : SELECT meta_value FROM wp_sitemeta WHERE meta_key = ‘wp_installer_network’ AND site_id = 1
  11. What is the difference between UNION and UNION ALL?
  12. Selecting COUNT(*) with DISTINCT
  13. What is a stored procedure?
  14. ORA-00904: invalid identifier
  15. The wait operation timed out. ASP
  16. Insert Data Into Temp Table with Query
  17. SQL SELECT WHERE field contains words
  18. Why do we need “Relationships” between tables at all?
  19. How to delete duplicate rows in SQL Server?
  20. Using group by on multiple columns
  21. How to declare a variable in MySQL?
  22. ORA-01843 not a valid month- Comparing Dates
  23. How do you trouble shoot a “Data type mismatch in criteria expression” error in MS Access 2010?
  24. Inserting data into a temporary table
  25. How to Select Top 100 rows in Oracle?
  26. CREATE VIEW must be the only statement in the batch
  27. SQL Server FOR EACH Loop
  28. How can I select the first day of a month in SQL?
  29. Warning: Null value is eliminated by an aggregate or other SET operation in Aqua Data Studio
  30. Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
  31. Oracle Apex column link from LOV values
  32. When to use “ON UPDATE CASCADE”
  33. MySQL combine two columns into one column
  34. ORA-01861: literal does not match format string
  35. SQL Server Insert if not exists
  36. SQL Server: Invalid Column Name
  37. Insert text with single quotes in PostgreSQL
  38. SQL Server IF NOT EXISTS Usage?
  39. String or binary data would be truncated. The statement has been terminated
  40. How to insert date values into table
  41. Error converting data type varchar to float
  42. How to concatenate text from multiple rows into a single text string in SQL Server
  43. Arithmetic overflow error converting numeric to data type numeric
  44. how to remove time from datetime
  45. “You tried to execute a query that does not include the specified aggregate function”
  46. How to write a SQL DELETE statement with a SELECT statement in the WHERE clause?
  47. How to round down to nearest integer in MySQL?
  48. How do I reset a sequence in Oracle?
  49. MySQL error: key specification without a key length
  50. SQL: Two select statements in one query
  51. How to query MongoDB with “like”
  52. Check if MySQL table exists without using “select from” syntax?
  53. MySQL equivalent of DECODE function in Oracle
  54. mysql update column with value from another table
  55. what is the difference between triggers, assertions and checks (in database)
  56. Replacing NULL with 0 in a SQL server query
  57. SQL Server dynamic PIVOT query?
  58. How to delete from multiple tables in MySQL?
  59. What does Include() do in LINQ?
  60. SQL Server “cannot perform an aggregate function on an expression containing an aggregate or a subquery”, but Sybase can
  61. Get everything after and before certain character in SQL Server
  62. Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null
  63. ORDER BY items must appear in the select list if SELECT DISTINCT is specified
  64. ROW_NUMBER() in MySQ
  65. LEFT function in Oracle
  66. Foreign key references invalid table
  67. What are database constraints?
  68. How do I view the Explain Plan in Oracle Sql developer?
  69. ORA-01779: cannot modify a column which maps to a non key-preserved table
  70. SUM OVER PARTITION BY
  71. SQLite string contains other string query
  72. Varchar invalid for Sum operator
  73. SQL: How to properly check if a record exists
  74. ora-06553 pls-306 wrong number or types of arguments in call to ‘ogc_x’
  75. SQL query to extract only the “current” wp_posts?
  76. Sorting search results by taxonomy terms
  77. SQL select of users by metadata
  78. How to properly sanitize strings without $wpdb->prepare?
  79. How to retrieve sticky post in raw sql?
  80. How to remove in the wordpress database all posts revisions except the last three?
  81. Backticks (`) Instead of Single Quotes (‘) in an SQL Statement?
  82. Delete duplicate rows from wordpress database where a column is duplicate in phpmyadmin
  83. $wpdb get_var issue
  84. Performance : Duplicating `add_action`
  85. posts_where Fails with More than One Custom Field in Query
  86. Inserting rows into a custom table, when plugin is activated
  87. WordPress database error: You have an error in your SQL syntax
  88. Restose content from revisions – sql query
  89. Why line returns are not reapply after doing esc_sql?
  90. WooCommerce database query : Get product category image path+name from thumbnail_id [closed]
  91. WordPress can I manually add columns in users table and display it in the default manage user plugin and registration form? [closed]
  92. Minimize Performance Issues of MultiSite Large Number of Tables Created
  93. How to avoid filling up an array each time I run a WP_query?
  94. Help me SELECT thumbnail from SQL and use
  95. Why is my insert row only inserting the final row from the loop into the database rather than just inserting one
  96. Search results sort order failing: set by date only
  97. SQL query: select duplicate posts – but using duplicate meta [duplicate]
  98. Creating a “forum” – showing last post or last commented post
  99. Replace Unwanted Space in Post Content URL
  100. How to write inner join using posts_clauses?

Leave a Comment