Forum Stats

  • 3,874,662 Users
  • 2,266,761 Discussions
  • 7,911,933 Comments

Discussions

How to use insert with update?

User_2TTDA
User_2TTDA Member Posts: 2 Green Ribbon

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

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,757 Red Diamond
    edited Jul 31, 2022 3:10AM

    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
    Frank Kulash Member, Moderator Posts: 42,757 Red Diamond
    edited Jul 31, 2022 3:25AM

    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.