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
Have a look at http://sqlite.org/lang_conflict.html. You want something like: insert or replace into Book (ID, Name, TypeID, Level, Seen) values ((select ID from Book where Name = “SearchName”), “SearchName”, …); Note that any field not in the insert list will be set to NULL if the row already exists in the table. This is why … Read more
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
Postgres 9.5 (released since 2016-01-07) offers an “upsert” command, also known as an ON CONFLICT clause to INSERT: INSERT … ON CONFLICT DO NOTHING/UPDATE It solves many of the subtle problems you can run into when using concurrent operation, which some other answers propose.
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
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
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
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