Upserting in MS-access
You can simulate an upsert in an Access by using an UPDATE query with a LEFT JOIN. update b left join a on b.id=a.id set a.f1=b.f1 , a.f2=b.f2 , a.f3=b.f3
upsert
Oracle: how to UPSERT (update or insert into a table?)
The MERGE statement merges data between two tables. Using DUAL allows us to use this command. Note that this is not protected against concurrent access. create or replace procedure ups(xa number) as begin merge into mergetest m using dual on (a = xa) when not matched then insert (a,b) values (xa,1) when matched then update … Read more
Is SELECT or INSERT in a function prone to race conditions?
It’s the recurring problem of SELECT or INSERT under possible concurrent write load, related to (but different from) UPSERT (which is INSERT or UPDATE). This PL/pgSQL function uses UPSERT (INSERT … ON CONFLICT .. DO UPDATE) to INSERT or SELECT a single row: CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int) LANGUAGE plpgsql AS … Read more
How to use RETURNING with ON CONFLICT in PostgreSQL?
The currently accepted answer seems ok for a single conflict target, few conflicts, small tuples and no triggers. It avoids concurrency issue 1 (see below) with brute force. The simple solution has its appeal, the side effects may be less important. For all other cases, though, do not update identical rows without need. Even if … Read more
How do I update if exists, insert if not (AKA “upsert” or “merge”) in MySQL?
Use INSERT … ON DUPLICATE KEY UPDATE. For example: INSERT INTO `usage` (`thing_id`, `times_used`, `first_time_used`) VALUES (4815162342, 1, NOW()) ON DUPLICATE KEY UPDATE `times_used` = `times_used` + 1
SQLite – UPSERT *not* INSERT or REPLACE
Assuming three columns in the table: ID, NAME, ROLE BAD: This will insert or replace all columns with new values for ID=1: INSERT OR REPLACE INTO Employee (id, name, role) VALUES (1, ‘John Foo’, ‘CEO’); BAD: This will insert or replace 2 of the columns… the NAME column will be set to NULL or the … Read more
Solutions for INSERT OR UPDATE on SQL Server
don’t forget about transactions. Performance is good, but simple (IF EXISTS..) approach is very dangerous. When multiple threads will try to perform Insert-or-update you can easily get primary key violation. Solutions provided by @Beau Crawford & @Esteban show general idea but error-prone. To avoid deadlocks and PK violations you can use something like this: begin … Read more
How to UPSERT (MERGE, INSERT … ON DUPLICATE UPDATE) in PostgreSQL?
9.5 and newer: PostgreSQL 9.5 and newer support INSERT … ON CONFLICT (key) DO UPDATE (and ON CONFLICT (key) DO NOTHING), i.e. upsert. Comparison with ON DUPLICATE KEY UPDATE. Quick explanation. For usage see the manual – specifically the conflict_action clause in the syntax diagram, and the explanatory text. Unlike the solutions for 9.4 and … Read more
Insert, on duplicate update in PostgreSQL?
PostgreSQL since version 9.5 has UPSERT syntax, with ON CONFLICT clause. with the following syntax (similar to MySQL) INSERT INTO the_table (id, column_1, column_2) VALUES (1, ‘A’, ‘X’), (2, ‘B’, ‘Y’), (3, ‘C’, ‘Z’) ON CONFLICT (id) DO UPDATE SET column_1 = excluded.column_1, column_2 = excluded.column_2; Searching postgresql’s email group archives for “upsert” leads to … Read more
Insert into a MySQL table or update if exists
Use INSERT … ON DUPLICATE KEY UPDATE QUERY: INSERT INTO table (id, name, age) VALUES(1, “A”, 19) ON DUPLICATE KEY UPDATE name=”A”, age=19