Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
How to use insert with update?

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
-
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.
-
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.