Databases
StackOverflow2013
Postsdbo
POSQL: DELETE data from self-referencing table in specific order
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL: DELETE data from self-referencing table in specific order
    text
    copied!<p>I have a table like this:</p> <pre><code>groupId guid PK parentId guid name left int right int </code></pre> <p>There is a foreign key from parentId to groupId (this is a self-referencing table).</p> <p>left and right are <a href="http://articles.sitepoint.com/print/hierarchical-data-database" rel="nofollow">MPTT</a> left/right values for maintaining a hierarchy. The important thing to note here is the bigger the left value, the more deeply-nested an item is (in other words: for any given item, its left value will always be bigger than the left value of all parents).</p> <hr> <p>I'm trying to write a quick DELETE statement to delete everything but the top-most group (which always has an empty GUID value), eg: </p> <pre><code>DELETE FROM [group] WHERE [groupId] &lt;&gt; '00000000-0000-0000-0000-000000000000'` </code></pre> <p>Unfortunately this doesn't work, as there are generally sub-groups below any given group that prevent deletion. If you run that DELETE query over and over, eventually it will delete everything, but this is obviously not a good solution. <br/><br/></p> <p>What I want is the equivalent of: </p> <pre><code>DELETE FROM [group] WHERE [groupId] &lt;&gt; '00000000-0000-0000-0000-000000000000' ORDER BY [left] DESC </code></pre> <p>Of course, that isn't allowed syntax, but effectively, it should delete the items with the largest left values first to ensure there are no groups below that will prevent deletion due to FK constraints. <br/><br/></p> <p>I also tried:</p> <pre><code>delete from [group] where groupid in ( select top 1000000 * from [group] where groupid &lt;&gt; '00000000-0000-0000-0000-000000000000' ORDER BY [left] desc ) </code></pre> <p>which is valid syntax (you can only use ORDER BY if you also use TOP) but doesn't actually cause the DELETE to happen in the order of returned rows, so it still doesn't work. <br/><br/></p> <p>Is this possible to do, without resorting to a cursor to delete rows one-by-one?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload