UPSERT in SQL

The UPSERT statement combines the INSERT and UPDATE statements into one statement.

If another row with the identical set of primary key values already exists for each row processed by the UPSERT statement, the other columns are modified to match the values from the record being UPSERTed.

The row is produced if no other row with the same set of primary key values exists, same as if the INSERT command had been used.

Upsert is available in MySQL, PostgreSQL, Apache Impala, Teradata, etc SQL products. In SQL Server, using “Merge” the same result can be obtained.

We can use UPSERT in different ways as described below:
1.  UPSERT using REPLACE
2.  UPSERT using INSERT IGNORE
3.  UPSERT using INSERT with ON DUPLICATE KEY UPDATE

Examples:
Replacing duplicate records
MySQL> REPLACE INTO Emp(EmpNo, Ename, Sal) VALUES (2187, ‘Scott Williams’, 2700);
Updating duplicate records
MySQL> INSERT INTO Emp( EmpNo, Ename, Sal)  VALUES (2188, ‘Raymond’, 2000)
ON DUPLICATE KEY UPDATE EmpNo = 2188, postTitle = ‘Raymond’, postPublished = 2000;
Ignoring duplicate record
MySQL> INSERT IGNORE INTO Emp(EmpNo, Ename, Sal) VALUES (2199, ‘Paul’, 2000);

Updating duplicate records
postgres=# INSERT INTO Emp values(2199, ‘Mike’), (2200, ‘Mark’) ON CONFLICT (key) UPDATE SET val = EXCLUDED.val;
Ignoring duplicate record
postgres=# INSERT INTO upsert values(1, ‘Tres’), (2, ‘Mono’) ON CONFLICT IGNORE;

Impala> UPSERT INTO kudu_table (pk, c1, c2, c3) VALUES (0, ‘hello’, 50, true), (1, ‘world’, -1, false);
Impala> UPSERT INTO production_table SELECT * FROM staging_table;
Impala> UPSERT INTO production_table SELECT * FROM staging_table WHERE c1 IS NOT NULL AND c2 > 0;
Note: The above examples taken from Impala docs, and please note that these statements only works for Impala tables that use the Kudu storage engine.

Happy learning!!