How to build an update statement to fill in missing data with records that contain that data on mult-CodePudding

I have thousands of records missing data in 2 columns in a table. I want to fill in those 2 columns using records that have that data by finding matches based on other columns. So use matching data to fill in those missing values.

Sample data:

maj_id	maj_name	parent_name	child_name	parent_id	parent_id_2	child_id
123456	XYZ_COMP	xyz_comp_pl	xyz_pl	987	5435	20-2
null	null	xyz_comp_pl	xyz_pl_2	987	5435	20-1
123457	ABC_COMP	abc_comp_pl	abc_pl	765	5843	34-1
123457	ABC_COMP	abc_comp_pl	abc_pl_2	765	5843	34-9
null	null	abc_comp_pl	abc_pl_3	765	5843	34-7
null	null	abc_comp_pl	abc_pl_4	765	5843	34-6

The null maj_id and maj_name is what I want to have updated with the maj_id and maj_name from columns that having matching parent_name, parent_id, and parent_id_2. I was able to identify records to update using something like the below query, but not sure how to create the update statement out of this.

select t.parent_id , t.maj_name

from test_table t inner join ( select parent_id , parent_name , parent_id_2 from test_table group by parent_id, parent_name, parent_id_2 having sum(case when maj_name is not null then 1 else 0 end) >= 1 and sum(case when maj_name is null then 1 else 0 end) >= 1 )D on t.parent_id = d.parent_id and t.parent_name = d.parent_name and t.parent_id_2 = d.parent_id_2 order by parent_id, maj_name ASC;

CodePudding user response：

MERGE INTO test_table
USING (SELECT b.rowid row_id,
              a.maj_id,
              a.maj_name
         FROM (SELECT parent_id,
                      parent_name,
                      parent_id_2,
                      maj_id,
                      maj_name,
                      ROW_NUMBER() OVER (PARTITION BY parent_id,parent_name,parent_id_2 ORDER BY CASE WHEN (maj_id IS NOT NULL) THEN 1 ELSE 0 END DESC, maj_id DESC) seq
                 FROM test_table) a,
               test_table b
         WHERE a.seq = 1
           AND a.parent_id = b.parent_id
           AND a.parent_name = b.parent_name
           AND a.parent_id_2 = b.parent_id_2
           AND b.maj_id IS NULL) src
ON (src.row_id = test_table.ROWID)
WHEN MATCHED THEN UPDATE SET maj_id = src.maj_id, maj_name = src.maj_name