Update data of two fields from another page-CodePudding

I have 2 tables soft_group(100M data) and soft(120M data). The fields of these tables are almost identical, I need to add 2 fields call_status, call_status_code to the soft_group table and fill them with data from the soft table. I added two fields, but I can't load them correctly, for some reason the data is duplicated and exceeds 240 million (should not be more than 100 million).

My script:

  INSERT /*  append enable_parallel_dml parallel(16)*/
    INTO SOFT_GROUP(CALL_STATUS,CALL_STATUS_CODE)
    SELECT --  parallel(16)
           rs.call_status AS call_status,
           rs.call_status_code AS call_status_code
    FROM SOFT s
    WHERE s.call_id = call_id
    AND s.start_time = start_time
    AND s.phonenumber = phonenumber;

Also I tried with update, but it takes a lot of time:

UPDATE SOFT_GROUP t1
   SET (call_status, call_status_code) = (SELECT t2.call_status, t2.call_status_code
                         FROM SOFT t2
                        WHERE t1.call_id = t2.call_id
                        AND t1.start_time = t2.start_time
                        AND t1.client_id = t2.client_id)
 WHERE EXISTS (
    SELECT 1
      FROM OFT t2
     WHERE t1.call_id IN t2.call_id
                        AND t1.start_time = t2.start_time
                        AND t1.client_id = t2.client_id );

CodePudding user response：

I'd rather say that you need to update current data, not insert new rows. If that's so, use merge.

It is unclear which column belongs to which table in code you posted so I kind of guessed it; I hope I managed to do that. If not, you should know how to uniquely match rows and which value goes into which column.

merge into soft_group a
using soft b
on (a.call_id         = b.call_id
    and a.start_time  = b.start_time
    and a.phonenumber = b.phonenumber
   )
when matched then update set
  a.call_status      = b.call_status,
  a.call_status_code = b.call_status_code;