Re: [Treebase-devel] Deleting Childless treeblocks

[William P. <wil...@ya...>]

On Jan 8, 2010, at 12:30 PM, Hilmar Lapp wrote:

> Or are there other tables that have a foreign key to treeblock

Yes -- the error is: 

ERROR: update or delete on table "treeblock" violates foreign key constraint "fk94d50830bfd107c3" on table "sub_treeblock"
DETAIL: Key (treeblock_id)=(2848) is still referenced from table "sub_treeblock". 

so... another approach is to first delete the sub_treeblock record and then delete the treeblock record. Can I express that in a single delete query? I'm guessing not. But if I do it in two delete queries, deleting sub_treeblock first will cause treeblock to lose the connection with the study table (all this hangs off of study_id = 22).  Which is why I'm thinking of building a big list of treeblock_ids, and then running two delete queries:

1. get [big list] like so:

SELECT tb.treeblock_id 
FROM study st JOIN submission sub ON (st.study_id = sub.study_id)
JOIN sub_treeblock stb ON (sub.submission_id = stb.submission_id)
JOIN treeblock tb ON (stb.treeblock_id = tb.treeblock_id)
LEFT JOIN phylotree pt ON (pt.treeblock_id = tb.treeblock_id)
WHERE pt.phylotree_id IS NULL
AND st.study_id = 22

Result is a list of 4492 distinct treeblock_ids -- reformat the result as comma-separated numbers.

2. First delete from the table that references treeblock.treeblock_id: DELETE FROM sub_treeblock WHERE treeblock_id IN ( [big list] ); 

3. Then delete from treeblock_id: DELETE FROM treeblock WHERE treeblock_id IN ( [big list] ); 

How does that sound?

bp