MERGE INTO tablename USING table_reference ON WHEN MATCHED THEN UPDATE SET column1 = value1 WHEN NOT MATCHED THEN INSERT VALUES ;
A right join is employed over the Target and the Source --where Target is the left table and Source is the right one. The four possible combinations yield these rules:
If the ON field in the Source matches the ON field in the Target, then UPDATE
If the ON field in the Source does not match the ON field in the Target, then INSERT
If the ON field does not exist in the Source but does exist in the Target, then no action is performed.
If the ON field does not exist in either the Source or Target, then no action is performed.
If multiple Source rows match a given Target row, an error is mandated by SQL:2003 standards. You cannot update a Target row multiple times with a MERGE statement
Some database implementations adopted the term "Upsert" to a database statement, or combination of statements, that inserts a record to a table in a database if the record does not exist or, if the record already exists, updates the existing record. This synonym is used in PostgreSQL and SQLite. It is also used to abbreviate the "MERGE" equivalent pseudo-code. It is used in Microsoft SQL Azure.
Other non-standard implementations
Some other database management systems support this, or very similar behavior, through their own, non-standard SQL extensions. MySQL, for example, supports the use of syntax which can be used to achieve a similar effect with the limitation that the join between target and source has to be made only on PRIMARY KEY or UNIQUE constraints, which is not required in the ANSI/ISO standard. It also supports REPLACE INTO syntax, which first attempts an insert, and if that fails, deletes the row, if exists, and then inserts the new one. There is also an IGNORE clause for the INSERT statement, which tells the server to ignore "duplicate key" errors and go on. SQLite's works similarly. It also supports REPLACE INTO as an alias for compatibility with MySQL. Firebird supports MERGE INTO though fails to throw an error when there are multiple Source data rows. Additionally there is a single-row version,, but the latter does not give you the option to take different actions on insert versus update IBM DB2 extends the syntax with multiple WHEN MATCHED and WHEN NOT MATCHED clauses, distinguishing them with ... AND some-conditionguards. Microsoft SQL Server extends with supporting guards and also with supporting Left Join via clauses. PostgreSQL supports merging via. CUBRID supports MERGE INTO statement. And supports the use of syntax. It also supports REPLACE INTO for compatibility with MySQL. Apache Phoenix supports UPSERT VALUES and UPSERT SELECT syntax.
A similar concept is applied in some NoSQL databases. E.g. in MongoDB the fields in a value associated with a key can be updated with an update operation. The update rasies an error if the key is not found. In the update operation it is possible to set the upsert flag: in this case a new value is stored associated to the given key if it does not exist, otherwise the whole value is replaced. In Redis the SEToperations sets the value associated with a given key. Redis does not know any detail of the internal structure of the value, so an update would have no meaning. So the SET operation has always a set or replace semantics.
