{"id":424,"date":"2012-11-27T21:05:47","date_gmt":"2012-11-28T04:05:47","guid":{"rendered":"http:\/\/www.1keydata.com\/blog\/?p=424"},"modified":"2012-11-27T21:05:47","modified_gmt":"2012-11-28T04:05:47","slug":"find-duplicates-in-sql","status":"publish","type":"post","link":"https:\/\/www.1keydata.com\/blog\/find-duplicates-in-sql.html","title":{"rendered":"Find Duplicates in SQL"},"content":{"rendered":"<p>SQL does not provide a built-in capability to find duplicates in a table. Fortunately, it is fairly easy to write a SQL query that does it. The idea is to count the number of occurrences for each value, and then use the HAVING condition to show only the values that appear more than once.<\/p>\n<p>SELECT COLUMN_NAME, COUNT(*)<br \/>\nFROM TABLE_NAME<br \/>\nGROUP BY COLUMN_NAME<br \/>\nHAVING (COUNT(*) &gt; 1);<\/p>\n<p><!--more-->To extend this a step further, if you want to retrieve the list of all records that have duplicate values, you can use the SQL statement below, which contains a <a href=\"http:\/\/www.1keydata.com\/sql\/sql-subquery.html\">subquery<\/a> and includes an inline view:<\/p>\n<p>SELECT *<br \/>\nFROM TABLE_NAME<br \/>\nWHERE COLUMN_NAME IN<br \/>\n(SELECT COLUMN_NAME FROM<br \/>\n(SELECT COLUMN_NAME, COUNT(*)<br \/>\nFROM TABLE_NAME<br \/>\nGROUP BY COLUMN_NAME<br \/>\nHAVING (COUNT(*) &gt; 1) VIEW_NAME<br \/>\n);<\/p>\n<p>Essentially the original query to get the list of values with more than one occurrence was set up as an inline view for the query that would get just the COLUMN_NAME (not the count). It&#8217;s necessary to do this because the IN clause we use means that the subquery should only return a single column.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Find out how to use SQL to find duplicates in a table.  In addition, learn how to retrieve all records that contain duplicates.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[247],"tags":[251,250,264,245,248],"_links":{"self":[{"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/posts\/424"}],"collection":[{"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/comments?post=424"}],"version-history":[{"count":5,"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/posts\/424\/revisions"}],"predecessor-version":[{"id":448,"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/posts\/424\/revisions\/448"}],"wp:attachment":[{"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/media?parent=424"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/categories?post=424"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/tags?post=424"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}