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. ORA-00904: invalid identifier
  14. Unknown column in ‘field list’ error on MySQL Update query
  15. Insert Data Into Temp Table with Query
  16. How to create Temp table with SELECT * INTO tempTable FROM CTE Query
  17. SQL SELECT WHERE field contains words
  18. Using group by on multiple columns
  19. Efficiently convert rows to columns in sql server
  20. How to delete duplicate rows in SQL Server?
  21. Nested select statement in SQL Server
  22. What’s the difference between VARCHAR and CHAR?
  23. How to split the name string in mysql?
  24. How do you trouble shoot a “Data type mismatch in criteria expression” error in MS Access 2010?
  25. Sql Server equivalent of a COUNTIF aggregate function
  26. Column name or number of supplied values does not match table definition
  27. Inserting data into a temporary table
  28. Simple PHP Pagination script
  29. ORA-12560: TNS:protocol adaptor error
  30. How can I select the first day of a month in SQL?
  31. Warning: Null value is eliminated by an aggregate or other SET operation in Aqua Data Studio
  32. SQL – HAVING vs. WHERE
  33. Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
  34. Oracle Apex column link from LOV values
  35. What’s the difference between RANK() and DENSE_RANK() functions in oracle?
  36. When to use “ON UPDATE CASCADE”
  37. How to SUM two fields within an SQL query
  38. DateTime2 vs DateTime in SQL Server
  39. ORA-01861: literal does not match format string
  40. Difference between numeric, float and decimal in SQL Server
  41. SQL Server Insert if not exists
  42. SQL Server: Invalid Column Name
  43. “ORA-01438: value larger than specified precision allowed for this column” when inserting 3
  44. ERROR: there is no unique constraint matching given keys for referenced table “bar”
  45. How do I query for all dates greater than a certain date in SQL Server?
  46. String or binary data would be truncated. The statement has been terminated
  47. Cast from VARCHAR to INT – MySQL
  48. Conversion failed when converting the varchar value ‘simple, ‘ to data type int
  49. postgresql: error duplicate key value violates unique constraint
  50. Is there a workaround for ORA-01795: maximum number of expressions in a list is 1000 error?
  51. ORA-06502: PL/SQL: numeric or value error: character string buffer too small
  52. “You tried to execute a query that does not include the specified aggregate function”
  53. Insert into a MySQL table or update if exists
  54. How to round down to nearest integer in MySQL?
  55. Convert timestamp to date in Oracle SQL
  56. How do I reset a sequence in Oracle?
  57. MySQL error: key specification without a key length
  58. SQL: Two select statements in one query
  59. Update statement with inner join on Oracle
  60. Check if MySQL table exists without using “select from” syntax?
  61. Can I use multiple “with”?
  62. Incorrect syntax near ”
  63. MySQL: Can’t create table (errno: 150)
  64. Join vs. sub-query
  65. Alternative to except in MySQL
  66. What does Include() do in LINQ?
  67. SQL Server “cannot perform an aggregate function on an expression containing an aggregate or a subquery”, but Sybase can
  68. Get everything after and before certain character in SQL Server
  69. LEFT function in Oracle
  70. Arithmetic overflow error converting varchar to data type numeric. ’10’ <= 9.00
  71. Difference between EXISTS and IN in SQL?
  72. What does * mean in sql?
  73. INSERT VALUES WHERE NOT EXISTS
  74. Using union and order by clause in mysql
  75. Difference between `load data inpath ` and `location` in hive?
  76. How to implement LIMIT with SQL Server? [duplicate]
  77. SQLite string contains other string query
  78. Cannot insert explicit value for identity column in table ‘table’ when IDENTITY_INSERT is set to OFF
  79. SQL select of users by metadata
  80. How to properly sanitize strings without $wpdb->prepare?
  81. WordPress SQL LIKE request doesn’t work for fields with special symbols
  82. Delete duplicate rows from wordpress database where a column is duplicate in phpmyadmin
  83. Performance : Duplicating `add_action`
  84. posts_where Fails with More than One Custom Field in Query
  85. Restose content from revisions – sql query
  86. Better way to migrate to server?
  87. Removing posts by sql
  88. Why line returns are not reapply after doing esc_sql?
  89. WooCommerce database query : Get product category image path+name from thumbnail_id [closed]
  90. WordPress can I manually add columns in users table and display it in the default manage user plugin and registration form? [closed]
  91. How to avoid filling up an array each time I run a WP_query?
  92. Help me SELECT thumbnail from SQL and use
  93. Why is my insert row only inserting the final row from the loop into the database rather than just inserting one
  94. Search results sort order failing: set by date only
  95. SQL query: select duplicate posts – but using duplicate meta [duplicate]
  96. Creating a “forum” – showing last post or last commented post
  97. Replace Unwanted Space in Post Content URL
  98. How to write inner join using posts_clauses?
  99. Firebase with WordPress instead of SQL?
  100. Want to delete woocommerce coupon in bulk from phpmyadmin based on published date

Leave a Comment