{"id":34596,"date":"2023-04-14T21:41:07","date_gmt":"2023-04-14T16:11:07","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-delete-duplicate-rows-using-certain-conditions-in-ms-sql-server\/"},"modified":"2023-04-14T21:41:07","modified_gmt":"2023-04-14T16:11:07","slug":"solved-delete-duplicate-rows-using-certain-conditions-in-ms-sql-server","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-delete-duplicate-rows-using-certain-conditions-in-ms-sql-server\/","title":{"rendered":"[Solved] Delete duplicate rows using certain conditions in MS SQL Server"},"content":{"rendered":"

Introduction<\/h2>\n

[ad_1]<\/p>\n

When working with data in MS SQL Server, it is often necessary to delete duplicate rows based on certain conditions. This can be done using a combination of the SELECT DISTINCT and DELETE statements. The SELECT DISTINCT statement is used to return only unique rows from a table, while the DELETE statement is used to delete the duplicate rows. In this tutorial, we will discuss how to delete duplicate rows using certain conditions in MS SQL Server.<\/p>\n

Solution<\/h2>\n

The following query can be used to delete duplicate rows using certain conditions in MS SQL Server:<\/p>\n

DELETE FROM table_name
\nWHERE row_id NOT IN (SELECT MIN(row_id)
\n FROM table_name
\n GROUP BY column1, column2, column3); <\/p>\n

[ad_2]
\n<\/p>\n

\n
\n
<\/div>\n
\n
\n

Using the window lead()<\/code> to get the next departure date, then we check date diff if less than 30 days then we remove the record :<\/p>\n

with cte as (\n  select *, \n      lead(stayid) over (partition by cid order by departure) as lead_stayid,\n      DATEDIFF(day, departure, lead(departure) over (partition by cid order by departure)) as date_diff\n  from mytable\n)\ndelete from mytable\nWhere stayid in (\n                select stayid\n                from cte\n                where date_diff is not null and date_diff < 30\n                );\n<\/code><\/pre>\n
Demo here<\/a><\/p>\n<\/p><\/div>\n
<\/div>\n<\/div>\n
            <\/span> <\/p><\/div>\n<\/div>\n
  <\/div>\n
Deleting Duplicate Rows Using Conditions in MS SQL Server<\/h1>\n
Duplicate rows can be a nuisance in any database, and Microsoft SQL Server is no exception. Fortunately, there are several ways to delete duplicate rows using conditions in MS SQL Server. In this article, we’ll discuss the different methods for deleting duplicate rows and provide examples of each.<\/p>\n
Using the DELETE Statement<\/h2>\n
The most straightforward way to delete duplicate rows is to use the DELETE statement. This statement allows you to specify the conditions for which rows should be deleted. For example, if you wanted to delete all rows with the same value in a certain column, you could use the following statement:<\/p>\n
DELETE FROM table_name\nWHERE column_name = value;<\/pre>\n
This statement will delete all rows with the specified value in the specified column. You can also use the DELETE statement to delete rows based on multiple conditions. For example, if you wanted to delete all rows with the same value in two columns, you could use the following statement:<\/p>\n
DELETE FROM table_name\nWHERE column_name1 = value1\nAND column_name2 = value2;<\/pre>\n
Using the DISTINCT Clause<\/h2>\n
Another way to delete duplicate rows is to use the DISTINCT clause. This clause allows you to select only distinct (unique) values from a table. For example, if you wanted to select only the distinct values from a certain column, you could use the following statement:<\/p>\n
SELECT DISTINCT column_name\nFROM table_name;<\/pre>\n
This statement will return only the distinct values from the specified column. You can also use the DISTINCT clause to select distinct values from multiple columns. For example, if you wanted to select only the distinct values from two columns, you could use the following statement:<\/p>\n
SELECT DISTINCT column_name1, column_name2\nFROM table_name;<\/pre>\n
Using the GROUP BY Clause<\/h2>\n
The GROUP BY clause is another way to delete duplicate rows. This clause allows you to group rows together based on a certain condition. For example, if you wanted to group rows together based on the value in a certain column, you could use the following statement:<\/p>\n
SELECT column_name\nFROM table_name\nGROUP BY column_name;<\/pre>\n
This statement will group all rows with the same value in the specified column together. You can also use the GROUP BY clause to group rows together based on multiple conditions. For example, if you wanted to group rows together based on the value in two columns, you could use the following statement:<\/p>\n
SELECT column_name1, column_name2\nFROM table_name\nGROUP BY column_name1, column_name2;<\/pre>\n
Conclusion<\/h2>\n
In this article, we discussed the different methods for deleting duplicate rows using conditions in MS SQL Server. We discussed the DELETE statement, the DISTINCT clause, and the GROUP BY clause. Each of these methods can be used to delete duplicate rows in MS SQL Server.<\/p>\n","protected":false},"excerpt":{"rendered":"
Introduction [ad_1] When working with data in MS SQL Server, it is often necessary to delete duplicate rows based on certain conditions. This can be done using a combination of the SELECT DISTINCT and DELETE statements. The SELECT DISTINCT statement is used to return only unique rows from a table, while the DELETE statement is … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[341,5732,500],"class_list":["post-34596","post","type-post","status-publish","format-standard","hentry","category-solved","tag-sql","tag-sql-delete","tag-sql-server"],"yoast_head":"\n[Solved] Delete duplicate rows using certain conditions in MS SQL Server - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-delete-duplicate-rows-using-certain-conditions-in-ms-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Delete duplicate rows using certain conditions in MS SQL Server - JassWeb\" \/>\n<meta property=\"og:description\" content=\"Introduction [ad_1] When working with data in MS SQL Server, it is often necessary to delete duplicate rows based on certain conditions. This can be done using a combination of the SELECT DISTINCT and DELETE statements. The SELECT DISTINCT statement is used to return only unique rows from a table, while the DELETE statement is ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-delete-duplicate-rows-using-certain-conditions-in-ms-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2023-04-14T16:11:07+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-delete-duplicate-rows-using-certain-conditions-in-ms-sql-server\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-delete-duplicate-rows-using-certain-conditions-in-ms-sql-server\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Delete duplicate rows using certain conditions in MS SQL Server\",\"datePublished\":\"2023-04-14T16:11:07+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-delete-duplicate-rows-using-certain-conditions-in-ms-sql-server\/\"},\"wordCount\":566,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"sql\",\"sql-delete\",\"sql-server\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-delete-duplicate-rows-using-certain-conditions-in-ms-sql-server\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-delete-duplicate-rows-using-certain-conditions-in-ms-sql-server\/\",\"name\":\"[Solved] Delete duplicate rows using certain conditions in MS SQL Server - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2023-04-14T16:11:07+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-delete-duplicate-rows-using-certain-conditions-in-ms-sql-server\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-delete-duplicate-rows-using-certain-conditions-in-ms-sql-server\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-delete-duplicate-rows-using-certain-conditions-in-ms-sql-server\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Delete duplicate rows using certain conditions in MS SQL Server\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/jassweb.com\/solved\/#website\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/jassweb.com\/solved\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"contentUrl\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] Delete duplicate rows using certain conditions in MS SQL Server - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-delete-duplicate-rows-using-certain-conditions-in-ms-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Delete duplicate rows using certain conditions in MS SQL Server - JassWeb","og_description":"Introduction [ad_1] When working with data in MS SQL Server, it is often necessary to delete duplicate rows based on certain conditions. This can be done using a combination of the SELECT DISTINCT and DELETE statements. The SELECT DISTINCT statement is used to return only unique rows from a table, while the DELETE statement is ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-delete-duplicate-rows-using-certain-conditions-in-ms-sql-server\/","og_site_name":"JassWeb","article_published_time":"2023-04-14T16:11:07+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-delete-duplicate-rows-using-certain-conditions-in-ms-sql-server\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-delete-duplicate-rows-using-certain-conditions-in-ms-sql-server\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Delete duplicate rows using certain conditions in MS SQL Server","datePublished":"2023-04-14T16:11:07+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-delete-duplicate-rows-using-certain-conditions-in-ms-sql-server\/"},"wordCount":566,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["sql","sql-delete","sql-server"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-delete-duplicate-rows-using-certain-conditions-in-ms-sql-server\/","url":"https:\/\/jassweb.com\/solved\/solved-delete-duplicate-rows-using-certain-conditions-in-ms-sql-server\/","name":"[Solved] Delete duplicate rows using certain conditions in MS SQL Server - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2023-04-14T16:11:07+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-delete-duplicate-rows-using-certain-conditions-in-ms-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-delete-duplicate-rows-using-certain-conditions-in-ms-sql-server\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-delete-duplicate-rows-using-certain-conditions-in-ms-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Delete duplicate rows using certain conditions in MS SQL Server"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/34596","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=34596"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/34596\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=34596"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=34596"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=34596"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}