How to use insert with update?

User_2TTDA

There are two tables in Oracle SQL Developer that need to be updated with the amounts of the subscribers' invoices, from values ​​from the INVOICE_ADJUSTMENT table and if the subscriber is not in the base, it must be included.

The main table that I need to update is the INVOICE_SUBSCRIBE where it has the fields (SUBSCRIBER, PLAN, VALUE) and the other one from where I will bring the info is the AJUSTE_INVOICE that has the fields (SUBSCRIBER, PLAN, NEW_VALUE).

I managed to do it in two parts with INSERT INTO and then UPDATE, but the idea is to make a single code and also I need to update the same table with ~300 million records and it is not possible to run the entire base at once. How can I sketch the bulk update routine/sql?

INSERT INTO INVOICE_SUBSCRIBER(COD_SUBSCRIBER, COD_PLAN)

SELECT

   A.COD_SUBSCRIBER

  , A.COD_PLAN

FROM INVOICE_ADJUSTMENT A

LEFT JOIN INVOICE_SUBSCRIBER B

ON A.COD_SUBSCRIBER = B.COD_SUBSCRIBER

WHERE B.COD_SUBSCRIBER IS NULL

AND AFTER RUNNING THE INSERT, I MAKE THE UPDATE:

INVOICE UPDATE_SUBSCRIBER

SET INVOICE_VALUE = NEW_VALUE

FROM INVOICE_ADJUSTMENT A

WHERE A.COD_SUBSCRIBER = INVOICE_SUBSCRIBER.COD_SUBSCRIBER

Frank Kulash

Hi, @User_2TTDA

Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements for all tables involved, relevant columns only) so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. For DML issues (such as INSERT and UPDATE) the CREATE TABLE and INSERT statements you post should show the tables as they exist before the DML begins, and the results will be the contents of the changed table after the DML is finished. Always post your complete Oracle version (e.g. 18.4.0.0.0).

I managed to do it in two parts with INSERT INTO and then UPDATE, but the idea is to make a single code 

That's just what MERGE was designed to do; it can INSERT the rows that don't already exist, and UPDATE the ones that do. For efficiency, only UPDATE the rows that don't already have the correct values.

Frank Kulash

Hi, @User_2TTDA

Depending on your exact requirements, you may want a MERGE statement like this:

MERGE INTO invoice_subscriber  dst
USING 	   invoice_adjustment  src
ON (dst.cod_subscriber = src.cod_subscriber)
WHEN NOT MATCHED THEN
  INSERT (    cod_subscriber,     cod_plan,     invoice_value)
  VALUES (src.cod_subscriber, dst.cod_plan, dst.new_value)
WHEN MATCHED THEN UPDATE
  SET     dst.invoice_value = src.new_value
  WHERE   DECODE  ( dst.invoice_value
  	  	  , src.new_value, 'SAME'
		   		 , 'DIFFERENT'
		  ) 		 = 'DIFFERENT'
;

Of course, I can't test it without sample tables.

This assumes that invoice_adjustment.cod_subscriber is unique.