Oracle DB equivalent of on duplicate key update
You would need to use a MERGE
. Something like
MERGE INTO users dest
USING( SELECT 1 user_id, 10 points FROM dual) src
ON( dest.user_id = src.user_id )
WHEN MATCHED THEN
UPDATE SET points = src.points
WHEN NOT MATCHED THEN
INSERT( user_id, points )
VALUES( src.user_id, src.points );
ON DUPLICATE KEY UPDATE in oracle or equiv (defining key/column?)
ON DUPLICATE KEY
is not a thing in Oracle. The closest equivalent is the MERGE
syntax. Here is an example on how to use it for your use case (I reduced the number of columns for the sake of readability):
merge into app.also_data a
using (select ? app_id, ? fac_ident, ? lg_name, ? basic_tp from dual) p
on (a.app_id = p.app_id)
when matched then
update set
a.fac_ident = p.fac_ident,
a.lg_name = p.lg_name,
a.basic_tp = p.basic_tp
when not matched then
insert(app_id, fac_ident, lg_name, basic_tp)
values(p.app_id, p.fac_ident, p.lg_name, p.basic_tp)
SQL query with ON DUPLICATE KEY UPDATE clarification needed
Oracle doesn't support the ON DUPLICATE KEY UPDATE
syntax. That appears to be MySQL-specific syntax.
Most likely, you would appear to want a MERGE
statement
MERGE INTO table1 t1
USING (SELECT col1, col2, col3
FROM table2) ss
ON (t1.col1 = ss.col1) -- whatever the key is
WHEN MATCHED THEN
UPDATE SET t1.col1 = ss.col1,
t1.col2 = ss.col2,
t1.col3 = ss.col3
WHEN NOT MATCHED THEN
INSERT( t1.col1, t1.col2, t1.col3 )
VALUES( ss.col1, ss.col2, ss.col3 )
Cx_Oracle equivalent of on duplicate key update
Is there anything like on duplicate key update
In Oracle, it is called MERGE
; have a look at the following example:
Table contents at the beginning:
SQL> select * From dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
MERGE statement:
SQL> merge into dept d
2 using (select deptno, dname, loc
3 from (select 10 deptno, 'ACC' dname, 'NY' loc from dual --> already exists, should be updated
4 union all
5 select 99 , 'NEW DEPT' , 'LONDON' from dual --> doesn't exists, should be inserted
6 )
7 ) x
8 on (d.deptno = x.deptno)
9 when matched then update set
10 d.dname = x.dname,
11 d.loc = x.loc
12 when not matched then insert (d.deptno, d.dname, d.loc)
13 values (x.deptno, x.dname, x.loc);
2 rows merged.
The result: as you can see, values for existing DEPTNO = 10
were updated, while the new DEPTNO = 99
was inserted into the table.
SQL> select * From dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACC NY
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
99 NEW DEPT LONDON
SQL>
I don't speak Python so I can't compose code you might use, but I hope that you'll manage to do it yourself.
UPDATE on INSERT duplicate primary key in Oracle?
MERGE is the 'do INSERT or UPDATE as appropriate' statement in Standard SQL, and probably therefore in Oracle SQL too.
Yes, you need a 'table' to merge from, but you can almost certainly create that table on the fly:
MERGE INTO Movie_Ratings M
USING (SELECT 1 AS mid, 3 AS aid, 8 AS rating FROM dual) N
ON (M.mid = N.mid AND M.aid = N.aid)
WHEN MATCHED THEN UPDATE SET M.rating = N.rating
WHEN NOT MATCHED THEN INSERT( mid, aid, rating)
VALUES(N.mid, N.aid, N.rating);
(Syntax not verified.)
Oracle DB insert and do nothing on duplicate key
You can use MERGE
. The syntax is a bit different from a regular insert though;
MERGE INTO test USING (
SELECT 1 AS id, 'Test#1' AS value FROM DUAL -- your row to insert here
) t ON (test.id = t.id) -- duplicate check
WHEN NOT MATCHED THEN
INSERT (id, value) VALUES (t.id, t.value); -- insert if no duplicate
An SQLfiddle to test with.
Insert...on duplicate key update in other databases(ParAccel) except MySql
The close equivalent in Standard SQL is MERGE
, introduced in SQL-99 and altered slightly in SQL:2003.
MERGE
is widely supported e.g. SQL Server 2008, Oracle 9, PostgreSQL, etc.
Update a column of same table if there is duplicate records
You can use the analytical function row_number() and rowid to get the rows:
UPDATE test_dup
SET done = 'error'
WHERE ROWID IN (SELECT ROWID
FROM (SELECT acc_num, tel_num, imsi, ROW_NUMBER () OVER (PARTITION BY acc_num, tel_num, imsi ORDER BY acc_num) AS ROW_NUMBER FROM test_dup)
WHERE ROW_NUMBER > 1)
Related Topics
SQL Database Design Best Practice (Addresses)
List Stored Functions That Reference a Table in Postgresql
Is It Faster to Access Data from Files or a Database Server
How to List Active Connections on Postgresql
Not Deferrable Versus Deferrable Initially Immediate
What Are the [Dis]Advantages of Using a Key/Value Table Over Nullable Columns or Separate Tables
Database Schema for Organizing Historical Stock Data
Postgresql With-Delete "Relation Does Not Exists"
Using Oracle SQL, How Does One Output Day Number of Week and Day of Week
Running a Stored Procedure with Nodejs and Mssql Package Error
What Is the SQL for 'Next' and 'Previous' in a Table
Selecting Specific Row Number in SQL
Libraries for Ado.Net to Rapidly Bulk Insert Data into a Database from a .CSV File
Autoincrement Fields on Databases Without Autoincrement Field
SQL Server Clustered Index - Order of Index Question
How to Import Excel Files with Different Names and Same Schema into Database
How to Get the Active Users Connected to a Postgresql Database via SQL
Is It Necessary to Use # for Creating Temp Tables in SQL Server