Performing batch update in self referencing table

Question

I have a self-referencing table (Items) with a nullable ReferenceId column. This is a max two-level hierarchy, with a parent (Type = 0), and possible children (Type = 1).

Example of Items Parent record: ReferenceId = null  Type = 0
Example of Items Child record: ReferenceId = Id of Parent   Type = 1

Currently the reference data in table is broken. I can recover references by writing a small software, but I wonder if I can use SQL (or Linq) query to do it in a single batch.

To understand what needs to be done, there is also a Containers table, which has a 1-to-many relationship with Items.

So, important columns in Items table will be Id, ContainerId, ReferenceId and Type

What I need to is:

UPDATE Items SET Referenceid = ?
FROM Items WHERE Type = 1

? should be an ID of the Items parent record, which is in the same container as child.

And child should updated ONLY if there is only 1 parent record in container.

So, if I have a structure like this:

Id   ContainerId     Referenceid      Type
1     1                NULL             0
2     1                NULL             0
3     1                NULL             1

I should not perform update since there are two parent records (Type=0) in container Id=1 (Id=1 and Id=2).

So, if I have a structure like this:

Id   ContainerId     Referenceid      Type
1     1                NULL             0
2     1                NULL             1
3     1                NULL             1

I should update both Id=2 and Id=3, setting ReferenceId = 1, since there is only 1 parent in container.

I hope this is clear. Any thoughts?

Answer 1

Using a common table expression to get only those parent items who are the only parent for a container, and then using an inner join with the common table expression:

;with p as (
  select 
      Id = min(Id)
    , ContainerId     
  from Items p
  where [Type] = 0
  group by ContainerId
  having count(*) = 1 /* <-- containers with only 1 parent */
)
update c
  set c.ReferenceId = p.Id
from Items c
  inner join p 
    on c.ContainerId = p.ContainerId
where c.Type = 1;

rextester demo: http://rextester.com/CDOIN71171

For item rows:

 (1,1,null,0) /* <-- 1 parent  in container 1 */
,(2,1,null,1)
,(3,1,null,1)
,(4,2,null,0) /* <-- 2 parents in container 2 */
,(5,2,null,0)
,(6,2,null,1)

returns:

+----+-------------+-------------+------+
| id | containerid | referenceid | type |
+----+-------------+-------------+------+
|  1 |           1 | NULL        |    0 |
|  2 |           1 | 1           |    1 |
|  3 |           1 | 1           |    1 |
|  4 |           2 | NULL        |    0 |
|  5 |           2 | NULL        |    0 |
|  6 |           2 | NULL        |    1 |
+----+-------------+-------------+------+