Oracle: how to UPSERT (update or insert into a table?)
An alternative to MERGE (the "old fashioned way"):
begin
insert into t (mykey, mystuff)
values ('X', 123);
exception
when dup_val_on_index then
update t
set mystuff = 123
where mykey = 'X';
end;
How to Update/insert records based on WHERE clause in Oracle
You can LEFT OUTER JOIN the new data to the existing data and find whether a row exists within 30 days and then, if it does, use the ROW_NUMBER analytic function to find the latest matching row and correlate the update using the ROWID pseudo-column:
MERGE INTO table_A dst
USING (
SELECT d.*,
a.ROWID AS rid,
ROW_NUMBER() OVER (ORDER BY a.date_column DESC NULLS LAST) AS rn
FROM ( SELECT '123' AS key_column,
TIMESTAMP '2023-01-05 00:00:11.644' AS date_column,
'MZM' AS ora
FROM DUAL ) d
LEFT OUTER JOIN table_A a
ON ( a.key_column = d.key_column
AND a.date_column BETWEEN d.date_column - INTERVAL '30' DAY
AND d.date_column + INTERVAL '30' DAY
)
) src
ON ( src.rid = dst.ROWID AND src.rn = 1)
WHEN MATCHED THEN
UPDATE
SET date_column = src.date_column,
ora = src.ora
WHEN NOT MATCHED THEN
INSERT (key_column, date_column, ora)
VALUES (src.key_column, src.date_column, src.ora);
Which, for the sample data:
CREATE TABLE table_a (
key_column VARCHAR2(255 BYTE) NOT NULL ENABLE,
date_column TIMESTAMP (6) NOT NULL ENABLE,
ORA VARCHAR2(100 BYTE)
);
INSERT INTO table_a (key_column, date_column, ora)
values ('123', TIMESTAMP '2022-12-31 00:00:11.644', 'MZM');
The the row updates to:
KEY_COLUMN DATE_COLUMN ORA 123 2023-01-05 00:00:11.644000 MZM
plsql creating an upsert/merge logic into table before insert from type object
As you have a PRIMARY KEY constraint over ID, you can use DUP_VAL_ON_INDEX exception and update the table based on the iteration. I was not able to test it but I could compile the procedure without issues.
CREATE OR REPLACE PROCEDURE PROCESS_SF_INV_ORACLE_T (p_data IN BLOB)
AS
TYPE t_hdr_tab IS TABLE OF XX_SF_ORACLE_INVOICE_T%ROWTYPE;
l_hdr_tab t_hdr_tab := t_hdr_tab();
l_clob CLOB;
l_dest_offset PLS_INTEGER := 1;
l_src_offset PLS_INTEGER := 1;
l_lang_context PLS_INTEGER := DBMS_LOB.default_lang_ctx;
l_warning PLS_INTEGER;
l_hdr_count PLS_INTEGER;
l_response VARCHAR2(4000);
BEGIN
-- Convert the BLOB to a CLOB.
DBMS_LOB.createtemporary(
lob_loc => l_clob,
cache => FALSE,
dur => DBMS_LOB.call);
DBMS_LOB.converttoclob(
dest_lob => l_clob,
src_blob => p_data,
amount => DBMS_LOB.lobmaxsize,
dest_offset => l_dest_offset,
src_offset => l_src_offset,
blob_csid => DBMS_LOB.default_csid,
lang_context => l_lang_context,
warning => l_warning);
APEX_JSON.parse(l_clob);
l_hdr_count := APEX_JSON.get_count(p_path => 'data');
FOR i IN 1 .. l_hdr_count LOOP
l_hdr_tab.extend;
l_hdr_tab(l_hdr_tab.last).ID := APEX_JSON.GET_VARCHAR2(p_path => 'data[%d].rows.ID', p0 => i);
l_hdr_tab(l_hdr_tab.last).NAME := APEX_JSON.GET_VARCHAR2 (p_path => 'data[%d].rows.NAME', p0 => i);
l_hdr_tab(l_hdr_tab.last).NET_INV__C := APEX_JSON.GET_VARCHAR2 (p_path => 'data[%d].rows.NET_INV__C', p0 => i);
l_hdr_tab(l_hdr_tab.last).PRICE__C := APEX_JSON.GET_NUMBER (p_path => 'data[%d].rows.PRICE__C', p0 => i);
l_hdr_tab(l_hdr_tab.last).QUANTITY__C := APEX_JSON.GET_NUMBER (p_path => 'data[%d].rows.QUANTITY__C', p0 => i);
l_hdr_tab(l_hdr_tab.last).LOCATION__C := APEX_JSON.GET_VARCHAR2 (p_path => 'data[%d].rows.LOCATION__C', p0 => i);
l_hdr_tab(l_hdr_tab.last).CREATEDDATE := APEX_JSON.GET_DATE (p_path => 'data[%d].rows.CREATEDDATE', p0 => i);
l_hdr_tab(l_hdr_tab.last).STATUS := 'U';
END LOOP;
-- Populate the tables.
FOR i IN l_hdr_tab.first .. l_hdr_tab.last LOOP
-- begin block
begin
INSERT INTO XX_SF_ORACLE_INVOICE_T VALUES l_hdr_tab(i);
exception when dup_val_on_index
then
update XX_SF_ORACLE_INVOICE_T U
set u.NAME = l_hdr_tab(i).name ,
u.NET_INV__C = l_hdr_tab(i).NET_INV__C ,
u.PRICE__C = l_hdr_tab(i).PRICE__C ,
u.QUANTITY__C = l_hdr_tab(i).QUANTITY__C ,
u.LOCATION__C = l_hdr_tab(i).LOCATION__C ,
u.CREATEDDATE = l_hdr_tab(i).CREATEDDATE,
u.STATUS = l_hdr_tab(i).STATUS
where U.ID = l_hdr_tab(i).ID ;
end;
END LOOP;
COMMIT;
DBMS_LOB.freetemporary(lob_loc => l_clob);
l_response := 'Success';
htp.prn('{"status":"'||l_response||'"}');
EXCEPTION
WHEN OTHERS THEN
l_response := 'Not Success';
htp.prn('{"status":"'||l_response||'"}');
-- HTP.print(SQLERRM);
END;
/Oracle SQL - when matched, update AND insert
Ok, so first off, I would write a query that produced the rows to be updated and/or inserted:
WITH performance AS (SELECT 1234 student_id, 5678 course_id, 'Mandatory' enrollment_type, 70 mark, 'ACTIVE' status, 2 VERSION FROM dual UNION ALL
SELECT 1234 student_id, 5678 course_id, 'Optional' enrollment_type, 70 mark, 'HISTORY' status, 1 VERSION FROM dual UNION ALL
SELECT 1234 student_id, 5678 course_id, 'Optional' enrollment_type, NULL mark, 'HISTORY' status, 0 VERSION FROM dual UNION ALL
SELECT 9876 student_id, 4597 course_id, 'Institutional' enrollment_type, 99 mark, 'ACTIVE' status, 1 VERSION FROM dual UNION ALL
SELECT 9876 student_id, 4597 course_id, 'Institutional' enrollment_type, NULL mark, 'HISTORY' status, 0 VERSION FROM dual),
group_enrollments AS (SELECT 4976555 group_id, 1234 student_id, 5678 course_id, 'Mandatory2' enrollment_type FROM dual UNION ALL
SELECT 6399875 group_id, 1234 student_id, 9034 course_id, 'Optional' enrollment_type FROM dual UNION ALL
SELECT 6399875 group_id, 9876 student_id, 4597 course_id, 'Institutional' enrollment_type FROM dual)
-- end of mimicking your tables with data in them
SELECT res.student_id,
res.course_id,
CASE WHEN dummy.id = 1 THEN res.new_enrollment_type
WHEN dummy.id = 2 THEN res.old_enrollment_type
END enrollment_type,
res.mark,
CASE WHEN dummy.id = 1 THEN 'ACTIVE'
WHEN dummy.id = 2 THEN 'HISTORY'
END status,
CASE WHEN dummy.id = 1 THEN res.new_version
WHEN dummy.id = 2 THEN res.old_version
END VERSION
FROM (SELECT ge.student_id,
ge.course_id,
ge.enrollment_type new_enrollment_type,
p.enrollment_type old_enrollment_type,
p.mark,
p.status,
p.version old_version,
nvl(p.version + 1, 0) new_VERSION
-- n.b. this may produce duplicates or unique constraint errors in a concurrent environment
FROM group_enrollments ge
LEFT OUTER JOIN PERFORMANCE p ON ge.student_id = p.student_id
AND ge.course_id = p.course_id
WHERE (p.status = 'ACTIVE' OR p.status IS NULL)
AND (p.enrollment_type != ge.enrollment_type OR p.enrollment_type IS NULL)) res
INNER JOIN (SELECT 1 ID FROM dual UNION ALL
SELECT 2 ID FROM dual) dummy ON dummy.id = 1
OR (dummy.id = 2
AND res.status = 'ACTIVE');
STUDENT_ID COURSE_ID ENROLLMENT_TYPE MARK STATUS VERSION
---------- ---------- --------------- ---------- ------- ----------
1234 5678 Mandatory2 70 ACTIVE 3
1234 9034 Optional ACTIVE 0
1234 5678 Mandatory 70 HISTORY 2This query first of all finds any rows that are brand new (i.e. rows in the group_enrollment table that don't have a row in the performance table) or have a different enrollment_type. These are the rows that need inserting or updating.
Once we know that, we can then join a dummy, 2-row table table such that we'll always join to the first dummy row regardless of whether we need to insert or update, but we'll only join to the second dummy row if we need to update. This means we will only ever have one row for an insert, but two rows for an update.
Then it's an easy matter of outputting the correct values based on the dummy.id (new values for the first dummy row, old values for the second dummy row.
Once we've done that, we know what data needs to be merged into the performance table, so now the merge statement will look something like:
merge into performance tgt
using (SELECT res.student_id,
res.course_id,
CASE WHEN dummy.id = 1 THEN res.new_enrollment_type
WHEN dummy.id = 2 THEN res.old_enrollment_type
END enrollment_type,
res.mark,
CASE WHEN dummy.id = 1 THEN 'ACTIVE'
WHEN dummy.id = 2 THEN 'HISTORY'
END status,
CASE WHEN dummy.id = 1 THEN res.new_version
WHEN dummy.id = 2 THEN res.old_version
END VERSION
FROM (SELECT ge.student_id,
ge.course_id,
ge.enrollment_type new_enrollment_type,
p.enrollment_type old_enrollment_type,
p.mark,
p.status,
p.version old_version,
nvl(p.version + 1, 0) new_VERSION
-- n.b. this may produce duplicates or unique constraint errors in a concurrent environment
FROM group_enrollments ge
LEFT OUTER JOIN PERFORMANCE p ON ge.student_id = p.student_id
AND ge.course_id = p.course_id
WHERE (p.status = 'ACTIVE' OR p.status IS NULL)
AND (p.enrollment_type != ge.enrollment_type OR p.enrollment_type IS NULL)) res
INNER JOIN (SELECT 1 ID FROM dual UNION ALL
SELECT 2 ID FROM dual) dummy ON dummy.id = 1
OR (dummy.id = 2
AND res.status = 'ACTIVE')) src
ON (tgt.student_id = src.student_id AND tgt.course_id = src.course_id AND tgt.status = src.status)
WHEN MATCHED THEN
UPDATE SET tgt.enrollment_type = src.enrollment_type,
tgt.version = src.version
WHEN NOT MATCHED THEN
INSERT (tgt.student_id, tgt.course_id, tgt.enrollment_type, tgt.mark, tgt.status, tgt.version)
VALUES (src.student_id, src.course_id, src.enrollment_type, src.mark, src.status, src.version);For clarification purposes, here's a very simple example of the conditional duplication of rows (we could also call it a partial cross join, since all rows in one table are joined to at least one row in the other):
WITH sample_data AS (SELECT 100 ID, NULL status FROM dual UNION ALL -- expect only one row
SELECT 101 ID, 'A' status FROM dual UNION ALL -- expect two rows
SELECT 102 ID, 'B' status FROM dual -- expect only one row
)
SELECT dummy.id dummy_row_id,
sd.id,
sd.status
FROM sample_data sd
INNER JOIN (SELECT 1 ID FROM dual UNION ALL
SELECT 2 ID FROM dual) dummy ON dummy.id = 1
OR (dummy.id = 2
AND sd.status = 'A')
ORDER BY sd.id, dummy.id;
DUMMY_ROW_ID ID STATUS
------------ ---------- ------
1 100
1 101 A
2 101 A
1 102 BYou can see that for the id=101 row from the sample_data "table", we have two rows, but the other two ids only have one row each.
Hopefully that clarifies things for you?
insert or update using if exists using merge oracle sql logic
when matched then
update tgt --> Incorrect syntax
set tgt.column1 = src.column1 [,...]
when not matched then
insert into tgt --> Incorrect syntaxYour syntax is incorrect for
MERGEstatement. The target table name is already mentioned inMERGE INTOsyntax, no need to specify the table name in theUPDATEandINSERTclause again. Correct syntax for update and insert clause is:WHEN MATCHED THEN
UPDATE SET TGT.product_nm = SRC.product_nm
WHEN NOT MATCHED THEN
INSERT (TGT.product_id, TGT.product_nm .....)
VALUES (SRC.product_id, SRC.product_nm .....);
Related Topics
Can't Connect to MySQL Server Error 111
What Is the Most Efficient/Elegant Way to Parse a Flat Table into a Tree
SQL Query to Concatenate Column Values from Multiple Rows in Oracle
When Do I Need to Use a Semicolon VS a Slash in Oracle Sql
How to Import an SQL File Using the Command Line in MySQL
MySQL Error: Key Specification Without a Key Length
How to Split the Name String in MySQL
Save Pl/Pgsql Output from Postgresql to a CSV File
Reset Identity Seed After Deleting Records in SQL Server
How to Find the MySQL My.Cnf Location
Optimize Group by Query to Retrieve Latest Row Per User
SQL Logic Operator Precedence: and and Or
How to Update If Exists, Insert If Not (Aka "Upsert" or "Merge") in MySQL
Difference Between Numeric, Float and Decimal in SQL Server
Why Would Someone Use Where 1=1 and ≪Conditions≫ in a SQL Clause
Conversion Failed When Converting Date And/Or Time from Character String While Inserting Datetime