Important: Backup and test this process thoroughly before applying in production.
First, stop ArchivesSpace and query for NULL top containers in the linking table:
SELECT tcl.id as tcl_id, tcl.top_container_id as tc_id, sc.id as sc_id, i.id as i_id
FROM top_container_link_rlshp tcl
JOIN sub_container sc ON tcl.sub_container_id = sc.id
JOIN instance i ON sc.instance_id = i.id
WHERE tcl.top_container_id IS NULL;The result should appear like this:
+--------+-------+-------+-------+
| tcl_id | tc_id | sc_id | i_id |
+--------+-------+-------+-------+
| 41919 | NULL | 41933 | 43396 |
| 41941 | NULL | 41955 | 43418 |
| 41955 | NULL | 41969 | 43432 |
| 41958 | NULL | 41972 | 43435 |
+--------+-------+-------+-------+
4 rows in set (0.00 sec)Next, delete each of the orphaned records using the returned ids:
DELETE FROM top_container_link_rlshp WHERE id IN (41919, 41941, 41955, 41958);
DELETE FROM sub_container WHERE id IN (41933, 41955, 41969, 41972);
DELETE FROM instance WHERE id IN (43396, 43418, 43432, 43435);Carefully check that the id values are correct before performing the delete.
Start ArchivesSpace and check the records (i.e. ~/accessions/439/edit).