{"id":418,"date":"2009-10-20T18:05:59","date_gmt":"2009-10-21T00:05:59","guid":{"rendered":"http:\/\/websitedesign.www.websitesinaflash.com\/?p=418"},"modified":"2009-10-20T18:05:59","modified_gmt":"2009-10-21T00:05:59","slug":"sql-delete-similar-or-identical-records","status":"publish","type":"post","link":"https:\/\/www.websitesinaflash.com\/2009\/10\/sql-delete-similar-or-identical-records\/","title":{"rendered":"SQL – Delete Similar or Identical Records"},"content":{"rendered":"

Deleting similar or identical records in SQL is a lifesaver. I found Brian Cryer’s “SQL How To…” about deleting similar or identical records in SQL<\/a>. It’s got a lot of information, including the code for MS-Access SQL.<\/p>\n

Here’s a quick explanation of what the following code does:<\/p>\n

It searches MyTable<\/em> for and deletes all rows where the dupField <\/em>is the same except for the row with the lowest uniqueField<\/em>.<\/p>\n

\n

Delete Similar Records<\/h3>\n

To delete similar records, i.e. where the records are not the same but one field is the same and only one copy needs to be preserved, try the following SQL:<\/p>\n

DELETE T1
\nFROM\u00c2 MyTable T1, MyTable T2
\nWHERE\u00c2 T1.dupField = T2.dupField
\nAND T1.uniqueField > T2.uniqueField<\/p><\/blockquote>\n<\/blockquote>\n

To break this down even further, this code would delete all rows in MyTable<\/em> that had the same value in dupField<\/em>.<\/p>\n

DELETET1
\nFROM MyTable T1, MyTable T2
\nWHERE T1.dupField = T2.dupField<\/p><\/blockquote>\n

This last line restricts the deleting to only rows that are also greater than another row with the same dupField<\/em><\/p>\n

AND T1.uniqueField > T2.uniqueField<\/p><\/blockquote>\n

Obviously, you would replace the field and table names to work for your table.<\/p>\n

Talk soon,
\nAshton Sanders<\/p>\n","protected":false},"excerpt":{"rendered":"Deleting similar or identical records in SQL is a lifesaver. I found Brian Cryer’s “SQL How To…” about deleting similar or identical records...","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"categories":[34,37],"tags":[119,120,121,122,304,332],"acf":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.websitesinaflash.com\/wp-json\/wp\/v2\/posts\/418"}],"collection":[{"href":"https:\/\/www.websitesinaflash.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.websitesinaflash.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.websitesinaflash.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.websitesinaflash.com\/wp-json\/wp\/v2\/comments?post=418"}],"version-history":[{"count":0,"href":"https:\/\/www.websitesinaflash.com\/wp-json\/wp\/v2\/posts\/418\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.websitesinaflash.com\/wp-json\/wp\/v2\/media?parent=418"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.websitesinaflash.com\/wp-json\/wp\/v2\/categories?post=418"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.websitesinaflash.com\/wp-json\/wp\/v2\/tags?post=418"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}