StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL - Clone a record and its descendants
text
Body
copied!<p>I would like to be able to clone a record and its descendants in the same table. An example of my table would be the following:</p> <p>Table1</p> <pre><code>id | parentid | name --------------------- 1 | 0 | 'Food' 2 | 1 | 'Taste' 3 | 1 | 'Price' 4 | 2 | 'Taste Requirements' </code></pre> <p>The "id" column is the primary key and auto-increments. The 'Food' record (i.e. where id = 1) has two records underneath it called 'Taste' and 'Price'. The 'Taste' record has a record underneath it called 'Taste Requirements'. I would like to be able to clone the 'Food' record so that Table1 would look like the following:</p> <p>Table1</p> <pre><code>id | parentid | name --------------------- 1 | 0 | 'Food' 2 | 1 | 'Taste' 3 | 1 | 'Price' 4 | 2 | 'Taste Requirements' 5 | 0 | 'Cookies' 6 | 5 | 'Taste' 7 | 5 | 'Price' 8 | 6 | 'Taste Requirements' </code></pre> <p>(where 'Cookies' is the name of the new category that I want to create). I am able to select all the descendants of 'Food' using:</p> <pre><code>with Table1_CTE( id, parentid, name ) as ( select t.id, t.parentid, t.name from Table1 t where t.id = 1 union all select t.id, t.parentid,t. name from Table1 t inner join Table1_CTE as tc on t.parentid = tc.id ) select id, parentid, name from Table1_CTE </code></pre> <p>and I am able to clone just the 'Food' record (i.e. where id = 1) using:</p> <pre><code>insert into Table1 ( parentid, name ) select ( parentid, 'Cookies' ) from Table1 where id = 1 </code></pre> <p>but I am having problems trying to combine the two queries to clone the descendants of 'Food'. Also, I am trying to avoid using stored procedures, triggers, curosrs, etc. Is what I am trying to do possible? I have seen some examples on the web but have been unable to apply them to my requirements.</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