{"id":248,"date":"2012-12-21T20:45:04","date_gmt":"2012-12-21T18:45:04","guid":{"rendered":"http:\/\/www.flip-design.de\/?p=248"},"modified":"2012-12-21T20:45:04","modified_gmt":"2012-12-21T18:45:04","slug":"tsql-partition-by-in-kombination-mit-einem-delete","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=248","title":{"rendered":"TSQL: Partition by in Kombination mit einem DELETE"},"content":{"rendered":"<p><a href=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2012\/12\/delete-button1.jpg\"><img decoding=\"async\" loading=\"lazy\" class=\"alignleft size-thumbnail wp-image-250\" title=\"delete-button1\" src=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2012\/12\/delete-button1-150x150.jpg\" alt=\"\" width=\"150\" height=\"150\" \/><\/a>Nicht ganz so spektakul\u00e4r, aber vor kurzem hatte ich das Problem, dass ich mit PARTITION BY Datens\u00e4tze selektieren wollte, die ich danach l\u00f6schen wollte.Vorab einfach mal, was macht den \u00fcberhaupt ein PARTITION BY? Eigentlich sehr simpel, ich selektiere Datens\u00e4tze, versehe diese mit einer Row_Number damit jeder DS eine eindeutige ID erh\u00e4lt und danach Gruppiere ich diese um somit Partitionen von Datens\u00e4tzen zu bilden.<\/p>\n<p>Beispiel anhand der Northwind Datenbank:<\/p>\n<pre lang=\"tsql\" escaped=\"true\">SELECT * FROM (\r\nSELECT CustomerID, PostalCode, City, row_number()\r\nOVER (Partition by PostalCode, City ORDER BY PostalCode, City) As Row\r\nFROM dbo.Customers) t\r\nWHERE t.Row &gt; 1<\/pre>\n<p>Ergebnis:<br \/>\n<a href=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2012\/12\/Partitionby.jpg\"><img decoding=\"async\" loading=\"lazy\" class=\"alignleft size-full wp-image-249\" title=\"Partitionby\" src=\"http:\/\/www.flip-design.de\/wp-content\/uploads\/2012\/12\/Partitionby.jpg\" alt=\"\" width=\"297\" height=\"96\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>Nun den, so haben wir alle Datens\u00e4tze wo ich eine doppelte PLZ und einen doppelten Ort habe, in einer Partition. Aber das Partion by ist ja schnell geschrieben, aber die Partition bilde ich ja in der Feldliste des SELECT&#8217;s, und nicht in der WHERE Condition. Wie l\u00f6sche ich nun diese Datens\u00e4tze?<\/p>\n<p>Ganz einfach &#8230; aber vielleicht auch etwas ungew\u00f6hlich(?)<\/p>\n<pre lang=\"tsql\" escaped=\"true\">DELETE FROM t from\u00a0 (\r\nSELECT CustomerID, PostalCode, City, row_number()\r\nOVER (Partition by PostalCode, City ORDER BY PostalCode, City) As Row\r\nFROM dbo.Customers2) t\r\nWHERE t.Row &gt; 1<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Nicht ganz so spektakul\u00e4r, aber vor kurzem hatte ich das Problem, dass ich mit PARTITION BY Datens\u00e4tze selektieren wollte, die ich danach l\u00f6schen wollte.Vorab einfach mal, was macht den \u00fcberhaupt ein PARTITION BY? Eigentlich sehr simpel, ich selektiere Datens\u00e4tze, versehe &hellip; <a href=\"https:\/\/www.flip-design.de\/?p=248\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[1,9,10,3],"tags":[],"_links":{"self":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/248"}],"collection":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=248"}],"version-history":[{"count":3,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/248\/revisions"}],"predecessor-version":[{"id":253,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/248\/revisions\/253"}],"wp:attachment":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=248"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=248"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=248"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}