A query to update group of related items with their main primary key (parent keys)

I Need to update the TS_PARENT_TS_ID column with the TS_ID column(Primary Key). Using the first rowas example, see column name (TS_TERM_TYPE_NAME), You have “MAIN” directly under it is “RELATED(s)”. all of which are related to each other, hence should have the same foreign keys in column (TS_PARENT_TS_ID)and in this case 1235. Another way to think about this is, in TS_TERM_TYPE_NAME column ALL “MAIN” that are followed with “RELATED(s)” belong to the same family and hence, should have the same (TS_PARENT_TS_ID). Can you help with a query that would update all Column (TS_PARENT_TS_ID) “RELATED” with (TS_PARENT_TS_ID) of “MAIN”.

Thank you!

Answer

If these are related through the last column — which seems likely — then:

update t
    set ts_parent_id = (select max(t2.ts_parent_id)
                        from t t2
                        where t2.ts_term_type = 'MAIN' and
                              t2.ts_notation = t.ts_notation
                       )
    where ts_term_type = 'RELATED';