I want to copy records from one table to another. The only records from table 1 that will be copied to table 2 are the ones that still dont exist in table 2. If duplicate records exists in Table 1 then only be copied to table 2 the record with the larger size name.
I could already implement a query that almost does what I want. The problem I have is when there are names with the same maximum size of characters. In these cases, my query returns more than one record and I just want to insert one new record in table 2.
Does anyone know how I can fix this?
Here is my code:
For x in (Select distinct xdd.id_t, xdd.name_t From table1 xdd Where xdd.id_t not in (Select distinct det.id_t2 From table2 det) And LENGTH(xdd.name_t) in (Select Max(LENGTH(xdd2.name_t)) From table1 xdd2 Where xdd2.id_t = xdd.id_t) ) Loop Insert into id_t2 (id_t2, name_t2) Values (x.id_t, x.name_t); End loop;
Can you give me an example to solve this? Sure. If I understood requirements correctly, then the
merge statement will look similar to this one:
row_number() analytic function to choose a duplicate record with longer
merge into table_two t2 using( select id_t , name_t from (select id_t , name_t , row_number() over(partition by id_t order by length(name_t) desc) as rn from table_one) q where q.rn = 1 ) t1 on (t2.id_t = t1.id_t) when not matched then insert(id_t, name_t) values(t1.id_t, t1.name_t)